带有 'Totals' 的 SQL 语句作为最后一列 [英] SQL Statement With 'Totals' as Final Column

问题描述

我正在尝试运行 SQL 查询，第一列是月".每列都有一个数字值(或月份数)，最后一列将显示总计".问题是，当我尝试让它们排序时，每次到达 Totals 列时都会出现错误，因为它正在寻找 INT 值.所以，例如，我有这个查询:

I'm trying to run an SQL query with the first column being 'month'. Each column will have a number value (or the month number) and the final column will say 'Totals'. The issue is that when I try to have them order, it gives me errors every time it reaches the Totals column because it is looking for an INT value. So, for example, I have this query:

SELECT CY.Month, CY.PaymentAmount_ThisYear, LY.PaymentAmount_LastYear FROM
(SELECT DATEPART(MM, Orders.OrderDate) AS Month, SUM(Orders.PaymentAmount) as PaymentAmount_ThisYear 
    FROM Orders WHERE DATEPART(YY, Orders.OrderDate) = DATEPART(YY, DATEADD(MM, -1, CURRENT_TIMESTAMP))
    GROUP BY DATEPART(MM, Orders.OrderDate)
) CY
INNER JOIN 
(SELECT DATEPART(MM, Orders.OrderDate) AS Month, SUM(Orders.PaymentAmount) as PaymentAmount_LastYear
    FROM Orders WHERE DATEPART(YY, Orders.OrderDate) = DATEPART(YY, DATEADD(MM, -13, CURRENT_TIMESTAMP))
    GROUP BY DATEPART(MM, Orders.OrderDate)
) LY
ON CY.Month = LY.Month
UNION
SELECT 'Totals' AS Month, CY.PaymentAmount_ThisYear, LY.PaymentAmount_LastYear FROM
(SELECT SUM(Orders.PaymentAmount) AS PaymentAmount_ThisYear
    FROM Orders WHERE DATEPART(YY, Orders.OrderDate) = DATEPART(YY, DATEADD(MM, -1, CURRENT_TIMESTAMP))
) CY,
(SELECT SUM(Orders.PaymentAmount) AS PaymentAmount_LastYear
    FROM Orders WHERE DATEPART(YY, Orders.OrderDate) = DATEPART(YY, DATEADD(MM, -13, CURRENT_TIMESTAMP))
) LY

如果我将总计"更改为 13，它会起作用并将总计放在底部，但该行显示的是13"而不是总计".

If I changed 'Totals' to 13, it works and puts the totals at the bottom but the row says '13' instead of 'Totals'.

然后我尝试的是在查询的最开始我改变了这个:

So what I then tried was at the very beginning of the query I changed this:

SELECT CY.Month

到

SELECT CAST(CY.Month AS VARCHAR(10)) AS Month

但是当我这样做时，它命令他们错了:

But when I do that it orders them wrong it goes:

1
10
11
5

因为它将它们识别为 varchar 值.我基本上是在寻找这个来按数字顺序排列月份，但最后一列说总计".

Because it's recognizing them as varchar values. I'm basically looking for this to order the months numerically but then have the last column say 'Totals'.

我也尝试过

订购者CASE ISNUMERIC(月)WHEN 1 THEN CAST(Month as INT)其他月份结束

ORDER BY CASE ISNUMERIC(Month) WHEN 1 THEN CAST(Month AS INT) ELSE Month END

但这给了我一个不明确的列名‘月’."错误.

But that was giving me an 'Ambiguous column name 'Month'.' error.

我有点卡住了，有什么想法吗?谢谢！

I'm sort of stuck, any ideas? Thank you!

推荐答案

我认为你可以大大简化你的查询并使用 GROUP BY 的 ROLLUP 选项来做你的总计，例如:

I think you can simplify your query considerably and use the ROLLUP option of GROUP BY to do your totals, e.g.:

SELECT 
      CASE 
        WHEN GROUPING(MONTH(orders.orderdate)) = 1 
        THEN 'Totals' 
        ELSE CONVERT(CHAR(2), MONTH(orders.orderdate)) 
      END AS [Month]
    , SUM(
        CASE 
            WHEN YEAR(orders.orderdate) = Datepart(yy, Dateadd(mm, -1, CURRENT_TIMESTAMP)) 
            THEN orders.paymentamount 
            ELSE 0.00
        END
        )   AS PaymentAmount_ThisYear 
    , SUM(
        CASE 
            WHEN YEAR(orders.orderdate) = Datepart(yy, Dateadd(mm, -13, CURRENT_TIMESTAMP)) 
            THEN orders.paymentamount 
            ELSE 0.00 
        END
        )   AS PaymentAmount_LastYear 
FROM   
    orders 
GROUP BY   
    MONTH(orders.orderdate)
WITH ROLLUP

这篇关于带有 'Totals' 的 SQL 语句作为最后一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！