CODEWITHSUNDEEP
sqlt-sqlsum
Andrew Gray
Andrew Gray

Reputation: 47

SUM from Multiple tables?

I am writing a query for a colleague at work which will look at a series of products. See what products have been sold and how many they have sold to which customer.

I have done the following query:

SELECT T0.[DocNum] AS "Invoice Number"
     , T0.[CardName] AS "Customer Name"
     , T1.[ItemCode] AS "Item Code"
     , T1.[Quantity] AS "Quantity" 
FROM OINV T0
    INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]
    INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
WHERE T2.[QryGroup7] = 'Y' AND (T0.DocDate>='[%0]' and T0.DocDate<='[%1]')
ORDER BY T0.[CardName], T1.[ItemCode]

A few notes to add on this... WHERE DocDate [%0] is a parameter for SAP B1, and Invoice number in the SELECT can be removed... this has been added just as a reference point until i resolve this problem.

I have tried selecting Docnum/Cardname/ as MAX and Quantity as SUM, then adding these to the GROUP BY section.

If I am to run this query it would show the following...

Andy - Milk - 40
Andy - Milk - 40
Andy - Milk - 20
Bob - Carrots - 30
Bob -Carrots - 50

I want to treat "Cardname and Itemcode" as distinct, but sum "quantity" To bring back a result of 

"Andy - milk - 100"
"Bob - Carrots - 80"

I have had a good google around and can't seem to find anything what will work, and things I have tried I have just made no difference.

Upvotes: 1

Views: 218

Answers (1)

Fahmi
Fahmi

Reputation: 37473

Try below with sum aggregation

SELECT T0.[CardName] AS "Customer Name"
, T1.[ItemCode] AS "Item Code"
, sum(T1.[Quantity]) AS "Quantity" 
FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
WHERE T2.[QryGroup7] = 'Y' AND (T0.DocDate>='[%0]' and T0.DocDate<='[%1]')
group by T0.[CardName],T1.[ItemCode]
ORDER BY T0.[CardName], T1.[ItemCode]

Upvotes: 1

Related Questions