Syntax Question Group By

[ghost2]

Hello all I am getting confused with this. I have a group by query where I am trying to sum a result but it is asking me for a field in a CASE statement where if I add it to the group by I get multiple records. Here is the query: The first part works if I take out the [0-30 Days]. I had to add the entire CASE statement to the group by to get it to work for me. Now I am trying to add the [0-30 Days] but it is asking for RM20101.docdate in the group by. If I add that there it will not sum all the results in one record anymore. Any ideas?

SELECT DISTINCT CASE WHEN ParentDescr IS NULL
THEN CASE WHEN ASCII(RM00101.STMTNAME) = 0 OR ASCII(RTRIM(STMTNAME)) IS NULL

THEN RTRIM(RM20101.CUSTNMBR) + ' ' + RTRIM(RM00101.CUSTNAME) + ' ' + RTRIM(RM00101.CNTCPRSN)
ELSE RTRIM(RM20101.CUSTNMBR) + ' ' + RTRIM(RM00101.STMTNAME) END
ELSE RTRIM(ParentDescr) END ParentName, Sum(RM20101.CURTRXAM) AS CurrentAmount,
CASE WHEN DATEDIFF(d, RM20101.DOCDATE, CAST(CAST(GETDATE() AS CHAR(11)) AS DATETIME)) >= 0
AND DATEDIFF(d, RM20101.DOCDATE, CAST(CAST(GETDATE() AS CHAR(11)) AS DATETIME)) < 30
THEN SUM(CONVERT(MONEY, RM20101.CURTRXAM)) ELSE 0 END [0-30 Days]
FROM ((((RM20101 INNER JOIN Sales_Orders_Master_File ON RM20101.DOCNUMBR = Sales_Orders_Master_File.InvoiceNo) INNER JOIN RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR) INNER JOIN tlkpPaymentMethods ON Sales_Orders_Master_File.PaymentMethod = tlkpPaymentMethods.PaymentMethod) INNER JOIN RM00101_EXT ON RM20101.CUSTNMBR = RM00101_EXT.CUSTNMBR) LEFT JOIN tblParentCompany ON RM00101_EXT.ParentId = tblParentCompany.ParentId
WHERE (((RM00101.CUSTCLAS)<>'DTC') AND ((tlkpPaymentMethods.PaymentType)='AR') AND ((RM20101.RMDTYPAL)=1))
GROUP BY CASE WHEN ParentDescr IS NULL
THEN CASE WHEN ASCII(RM00101.STMTNAME) = 0 OR ASCII(RTRIM(STMTNAME)) IS NULL

THEN RTRIM(RM20101.CUSTNMBR) + ' ' + RTRIM(RM00101.CUSTNAME) + ' ' + RTRIM(RM00101.CNTCPRSN)
ELSE RTRIM(RM20101.CUSTNMBR) + ' ' + RTRIM(RM00101.STMTNAME) END
ELSE RTRIM(ParentDescr) END
HAVING (((Sum(RM20101.CURTRXAM))>0.01))

 

[skuhlman]

If I'm reading your code right, the PARENTNAME is what you want to group by, and you have that done properly. The CURRENTAMOUNT is already the result of the aggregate function SUM() which you set up. The [0-30days] can be used in either of two ways. The first way is what the syntax checker is telling you to do... add it to the group by function which will give you results at a more granular level than you want. The second option is to make that field the result of an aggregate function. Assuming all of the values are the same, you can make it an aggregate function just by putting MAX(....) as [0-30days] where the ... is the case statement you already have there. The result is that the MAX of a constant value will result in that same value.