how to use group by in query

[Former Member]

In this query i want group by item description and quatity sum

SELECT T0.[Dscription], T0.[Quantity], T0.[OpenSum], T1.[DocNum], T2.[State] FROM DLN1 T0 INNER JOIN ODLN T1 ON T0.DocEntry = T1.DocEntry, CRD1 T2 WHERE T1.[CardCode] = T2.[CardCode] AND T1.[DocNum] Like '1_%%'

[Former Member · Accepted solution]

When you use group by, you have to limit the fields to those only with the same properties. Your example query would be too detailed to group. You may group only for State or only for items but not with Document Number.

Thanks,

Gordon

[former_member204969]

I'm not sure what you want, but you can start with something like this:

SELECT T0.Dscription, sum(T0.Quantity), sum(T0.OpenSum), T1.DocNum/*, T2.State*/
FROM DLN1 T0 INNER JOIN ODLN T1 ON T0.DocEntry = T1.DocEntry, CRD1 T2
WHERE T1.CardCode = T2.CardCode AND T1.DocNum Like '1_%%'
Group by T1.DocNum,T0.Dscription with rollup
Order by T1.DocNum,T0.Dscription

[Former Member]

Hi YYREDDY,

When you use the GROUP clause, all the fields in the SELECT clause must either figure in the GROUP clause or be part of an aggregate function (AVG, SUM, COUNT, ...).

SELECT 
    T0.Dscription, SUM(T0.Quantity), SUM(T0.OpenSum) 
FROM 
    DLN1 T0 INNER JOIN 
    ODLN T1 ON T0.DocEntry = T1.DocEntry, CRD1 T2 
WHERE 
    T1.CardCode = T2.CardCode AND T1.DocNum Like '1_%%'
GROUP BY
    T0.Dscription

Regards,

Vítor Vieira