Search code examples
sqlsql-serversumwindow-functions

How to select multiple columns and group by one column

I want to sum C.AMOUNT based on the number column. For example the number column in the same 4 numbers, then I want to sum the column C.AMOUNT based on the same number in the number column and display the summed column in the FINAL_AMOUNT column

enter image description here

    SELECT A.NUMBER, 
       B.AGE, 
       C.PRODUCT_NAME, 
       SUM(C.AMOUNT) AS FINAL_AMOUNT, 
       (CASE
            WHEN C.PRODUCT_NAME LIKE '%D%'
            THEN 'Y'
            ELSE 'N'
        END) AS D, 
       (CASE
            WHEN C.PRODUCT_NAME LIKE '%E%'
            THEN 'Y'
            ELSE 'N'
        END) AS E, 
       (CASE
            WHEN C.PRODUCT_NAME LIKE '%F%'
            THEN 'Y'
            ELSE 'N'
        END) AS F, 
       (CASE
            WHEN C.PRODUCT_NAME LIKE '%G%'
                 OR C.PRODUCT_NAME LIKE '%H%'
                 OR C.PRODUCT_NAME LIKE '%J%'
            THEN 'Y'
            ELSE 'N'
        END) AS J
FROM [A].[DBO].[A]
     LEFT JOIN [B].[DBO].[B] B ON A.NUMBER = B.NUMBER
     LEFT JOIN [C].[DBO].[C] C ON A.NUMBER = C.NUMBER
WHERE B.REPORT_DATE = '20200728'
GROUP BY A.NUMBER;

That is the query I used but I find error like this:

Msg 8120, Level 16, State 1, Line 1 Column 'B.DBO.B' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Solution

  • It looks like you want a window sum rather than aggregation.

    That is, replace:

    SUM(C.amount) as FINAL_AMOUNT

    With:

    SUM(C.amount) OVER(PARTITION BY A.NUMBER) as FINAL_AMOUNT

    Accordingly, you need to remove the GROUP BY clause from the query.