sql-servercoldfusiongroup-bycfoutput

group inside sql code rather than in cfoutput


I have a sql-server code of cities as rows and months as columns, there are sums by months but there are no sum by the city_id, I mean I have to count all the sum by city inside the output, and group it by city_id, anyway here is the sql code:

SELECT 
    SC.CITY_ID,SC.CITY_NAME, DATEPART(MM,I.INVOICE_DATE) AS INVOICE_MONTH,
    JAN=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=1 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    FEB=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=2 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    MAR=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=3 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    APR=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=4 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    MAY=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=5 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    JUN=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=6 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    JUL=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=7 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    AUG=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=8 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    SEP=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=9 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    OCT=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=10 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    NOV=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=11 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
    DEC=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=12 THEN COALESCE(NETTOTAL,0) ELSE 0 END)
FROM 
    SETUP_CITY SC
    LEFT OUTER JOIN COMPANY C ON C.CITY = SC.CITY_ID
    LEFT OUTER JOIN #DSN2_ALIAS#.INVOICE I ON I.COMPANY_ID = C.COMPANY_ID AND I.INVOICE_CAT IN (50,52,53,531,532,56,58,561,54,55,51,63,48) AND I.PURCHASE_SALES = 1
WHERE SC.COUNTRY_ID=1
GROUP BY SC.CITY_ID,SC.CITY_NAME,I.INVOICE_DATE
ORDER BY SC.CITY_ID,SC.CITY_NAME,I.INVOICE_DATE

and output:

<cfoutput query="get_top_sales_TOTAL" group="CITY_ID">

If I don't define the group by city_id, there are multiple rows of one city displayed. How is it possible to group the values (sum up) inside the sql code? So that I won't have to use the group statement inside the cfoutput?

Here is the screenshots to make it more clear, here's the screenshot with defined group="city_id"

enter image description here

If I don't define the group="city_id"

enter image description here


Solution

  • Mark is right about the invoice_month, there is no point for doing this. Your "correct" sql should be

    SELECT 
            SC.CITY_ID,SC.CITY_NAME,
            TOTAL=SUM(COALESCE(NETTOTAL,0)),
            JAN=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=1 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
            FEB=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=2 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
            MAR=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=3 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
            APR=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=4 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
            MAY=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=5 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
            JUN=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=6 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
            JUL=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=7 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
            AUG=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=8 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
            SEP=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=9 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
            OCT=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=10 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
            NOV=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=11 THEN COALESCE(NETTOTAL,0) ELSE 0 END),
            DEC=SUM(CASE WHEN DATEPART(MM,I.INVOICE_DATE)=12 THEN COALESCE(NETTOTAL,0) ELSE 0 END)
    FROM 
            SETUP_CITY SC
                LEFT OUTER JOIN COMPANY C ON C.CITY = SC.CITY_ID
                LEFT OUTER JOIN #DSN2_ALIAS#.INVOICE I ON I.COMPANY_ID = C.COMPANY_ID 
                        AND I.INVOICE_CAT IN (50,52,53,531,532,56,58,561,54,55,51,63,48) 
                        AND I.PURCHASE_SALES = 1
    WHERE  SC.COUNTRY_ID=1
    GROUP BY SC.CITY_ID, SC.CITY_NAME
    ORDER BY SC.CITY_ID,SC.CITY_NAME