Search code examples
sqldb2ibm-midrangesumifs

SQL Sumif functions / GROUP BY & SUM?

I have been stuck on this for a while now and cannot for the life of me understand why the SQL is not pulling through what I want. It seems simple and I know it's simple but I can't get the right numbers.

I have some code to pull data from joined tables on our database showing customer numbers, products purchased, date purchased along with product price and quantity of that product line. When I set between dates and GROUP by date, it works perfectly and pulls through all the right info.

What I want is to GROUP by product, summing the quantity of products purchased within a date range for each customer. When I try to sum(SIL.ILQTY) the sum over calcualtes this value.......I have tried Sum with CASE which also fails. I have the code below and have filter the records to make testing easier so only shows one product and one customer with 7 purchase dates for that product.

The following code shows records by date which works:

SELECT SIH.SICUST, SIL.ILPROD, date(SIL.ILDATE), SIL.ILNET, SIL.ILQTY,
       SIL.ILQTY*SIL.ILNET, SIH.SICUST || ' ' ||  rtrim(SIL.ILPROD) || ' '
FROM NWBAKERS.BPCSF.IIM IIM,
     NWBAKERS.BPCSF.SIH SIH,
     NWBAKERS.BPCSF.SIL SIL
WHERE SIH.SIINVN = SIL.ILINVN
  AND IIM.IPROD = SIL.ILPROD
  AND ((date(SIL.ILDATE) between '02/01/2016' and '02/29/2016'))
  and SIL.ILWHS ='PR'
  and SIL.ILQTY > '0'
  and SIH.SICUST = '40634'
  and SIL.ILPROD = '81039'
GROUP BY SIH.SICUST, SIL.ILPROD, date(SIL.ILDATE), SIL.ILQTY, SIL.ILNET

The problem is when I remove the date(SIL.ILDATE) from the group by statement and sum(SIL.ILQTY) in the select statement in order to get the Unique customer number which purchased the total number of unique products(ILQTY) in the date range specified.

Can anyone point me in the right direction please????

Sample Data Image for above:

image

Data with Date removed from Group by and sum(ILQTY) in select statement:

image2

Desired result for one product by customer

Image shows the added fields for invoice number and order number, giving duplicate lines for date/quantity/invoice as order number are different. The sum of these qty values is what my SQL code was summing previously.

Solution

  • I found a way to get this to work, was a bit different than I thought but it works well and return perfect information......Thanks to Everyone who tried to help with this Question!

    SELECT T.SICUST, T.ILPROD, SUM(Quantity) as Num_Products 
FROM
(
SELECT SIH.SICUST, SIL.ILPROD, SIL.ILNET, MAX(SIL.ILQTY) as Quantity
FROM NWBAKERS.BPCSF.IIM IIM,
     NWBAKERS.BPCSF.SIH SIH,
     NWBAKERS.BPCSF.SIL SIL
WHERE SIH.SIINVN = SIL.ILINVN
AND IIM.IPROD = SIL.ILPROD
AND ((date(SIL.ILDATE) between '02/01/2016' and '02/29/2016'))
and SIL.ILWHS ='PR'
and SIL.ILQTY > '0'
and SIH.SICUST = '40634'
and SIL.ILPROD = '81039'
GROUP BY SIH.SICUST, SIL.ILPROD, SIL.ILNET, SIL.ILINVN
) AS T
GROUP BY T.SICUST, T.ILPROD

    The problem was the duplicate lines in our tables from having multiple orders on the same invoice ID, from which I took the max value, then did a sum of those values after stripping out the date in the group by statement. Hope somebody else may find this useful!