I tried to have a running total row at the bottom of my table by running this following SQL code.
<code>SELECT T1.[ItemCode],T4.[ItmsGrpNam],CASEWHEN T3.[OnHand]- SUM(T1.[OpenQty])< SUM(T1.[OpenQty])THEN T3.[OnHand]- SUM(T1.[OpenQty])ELSE SUM(T1.[OpenQty])ENDAs'Missing Quantity'FROM ORDR T0 INNERJOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNERJOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNERJOIN OITW T3 ON T2.ItemCode = T3.ItemCode INNERJOIN OITB T4 ON T2.ItmsGrpCod=T4.ItmsGrpCod WHERE T3.[WhsCode]= T1.[WhsCode]and T0.[DocDueDate]<='[%D1]'and(T4.[ItmsGrpNam]='Commercial'Or T4.[ItmsGrpNam]='Residentiel')GROUPBY T1.[ItemCode],T3.[OnHand],T4.[ItmsGrpNam]HAVING T3.[OnHand]- SUM(T1.[OpenQty])<0Or SUM(T1.[OpenQty])<0UNIONALLSELECT'See','Total Missing Quantity',CASEWHEN T3.[OnHand]- SUM(T1.[OpenQty])< SUM(T1.[OpenQty])THEN T3.[OnHand]- SUM(T1.[OpenQty])ELSE SUM(T1.[OpenQty])ENDFROM ORDR T0 INNERJOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNERJOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNERJOIN OITW T3 ON T2.ItemCode = T3.ItemCode INNERJOIN OITB T4 ON T2.ItmsGrpCod=T4.ItmsGrpCod WHERE T3.[WhsCode]= T1.[WhsCode]and T0.[DocDueDate]<='[%D1]'and(T4.[ItmsGrpNam]='Commercial'Or T4.[ItmsGrpNam]='Residentiel')
I have this error message ' 1). [Microsoft] [SQL Server Native Client 11.0] [SQL Server] The 'OITW.OnHand' column is not valid in the selection list because it is not contained in an aggregate function or in the clause BY GROUP. 2). [Microsoft] [SQL Server Native Client 11.0] [SQL Server] Can not prepare statements. 'User Defined Values' (CSHS)'
When I try to
<code>GROUPBY T1.[ItemCode],T3.[OnHand],T4.[ItmsGrpNam]HAVING T3.[OnHand]- SUM(T1.[OpenQty])<0Or SUM(T1.[OpenQty])<0
In the second part of the query, I get multiple rows when I only want one that sum all.
I spent the day to figure this out and there's multiple answers on the NET but I don't know what i'm doing wrong.
Does anyone can help me with this issue? Thanks in advance.