在 sql 代码中而不是在 cfoutput 中进行分组

发布于 2025-01-04 18:01:54 字数 2099 浏览 2 评论 0原文

我有一个城市作为行、月份作为列的 SQL 服务器代码，有按月计算的总和，但没有按 city_id 计算的总和，我的意思是我必须按城市计算所有总和输出，并按 city_id 对其进行分组，无论如何，这里是 sql 代码：

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

和输出：

如果我不这样做't通过city_id定义组，一个城市显示多行。如何对 sql 代码中的值进行分组（求和）？这样我就不必在 cfoutput 中使用 group 语句？

这是为了更清楚起见的屏幕截图，这是定义 group="city_id"

enter 的屏幕截图此处图像描述

如果我未定义 group="city_id"

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

﹏雨一样淡蓝的深情 2025-01-11 18:01:54

马克关于发票月份的说法是正确的，这样做没有意义。你的“正确”sql 应该是

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

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

回复收藏 0 原文

找回味觉 2025-01-11 18:01:54

您的问题是 group by 和 order by 中的“invoice_date”将产生多行。试试这个：

GROUP BY SC.CITY_ID, SC.CITY_NAME, DATEPART(MM,I.INVOICE_DATE)
ORDER BY SC.CITY_ID, SC.CITY_NAME,  INVOICE_MONTH

无法尝试，所以我无法调试它 - 但基本上你的组中不能有单独的项目来扩展行的唯一性（就像“发票日期”可能会做的那样）。祝你好运！

Your problem is that "invoice_date" in the group by and order by are going to produce multiple rows. Try this:

GROUP BY SC.CITY_ID, SC.CITY_NAME, DATEPART(MM,I.INVOICE_DATE)
ORDER BY SC.CITY_ID, SC.CITY_NAME,  INVOICE_MONTH

Can't try it so I can't debug it - but basically your GROUP can't have individual items in it that expand the uniqueness of the row (as an "invoice date" would likely do). Good luck!

回复收藏 0 原文

~没有更多了~