Query with GROUP BY function

[Former Member]

Hi all,

I have the following query:

SELECT T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[LineTotal] FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[CardCode] >=[%0] and T0.[CardCode] <=[%1] and T0.[DocDate] >=[%2] and T0.[DocDate] <=[%3]

GROUP BY T0.CardCode, T0.CardName, T1.ItemCode, T1.Dscription, T1.Quantity, T1.LineTotal

Currently it is returning something along the lines:

#	Customer/Supplier Code	Customer/Supplier Name	Item No.	Item/Service Description	Quantity	Row Total
1	C20000	Norm Thompson	A00001	IBM Infoprint 1312	1	300
2	C20000	Norm Thompson	A00001	IBM Infoprint 1312	1	1,500.00
3	C20000	Norm Thompson	A00001	IBM Infoprint 1312	5	1,500.00
4	C20000	Norm Thompson	A00001	IBM Infoprint 1312	10	3,000.00
5	C20000	Norm Thompson	A00001	IBM Infoprint 1312	20	6,000.00
6	C20000	Norm Thompson	A00001	IBM Infoprint 1312	25	7,500.00
7	C20000	Norm Thompson	A00001	IBM Infoprint 1312	30	9,000.00

Where as I want it to return something along the lines of:

#	Customer/Supplier Code	Customer/Supplier Name	Item No.	Item/Service Description	Quantity	Row Total
1	C20000	Norm Thompson	A00001	IBM Infoprint 1312	92	28800

I want it to be grouped by Item code, quantity and line total.  Can someone please help on this one please?

Have tried to group this together and take out card code and/or card name but will not allow and wont return what I am looking for.

Thanks very much,

Brian

[former_member217514 · Accepted solution]

SELECT T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], SUM(T1.[Quantity]), SUM(T1.LineTotal)

FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[CardCode] >=[%0] and T0.[CardCode] <=[%1] and T0.[DocDate] >=[%2] and T0.[DocDate] <=[%3]

GROUP BY T0.CardCode, T0.CardName, T1.ItemCode, T1.Dscription

[Former Member · Accepted solution]

Thanks Jitin and Jeff

[jitin_chawla]

Hi,

Check this :

SELECT T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], sum(T1.[Quantity]),

sum(T1.[LineTotal])

FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[CardCode] >=[%0] and T0.[CardCode] <=[%1] and T0.[DocDate] >=[%2] and T0.[DocDate] <=[%3]

GROUP BY T0.CardCode, T0.CardName, T1.ItemCode, T1.Dscription

Kind Regards,

Jitin

SAP Business One Forum Team