时间戳

首页 > 解决方案 > 使用 CASE 语句对 2 行求和

sql - 使用 CASE 语句对 2 行求和

问题描述

我想显示Subscriptional行的 SUM 并显示为一个Subscriptional。我的脚本如下所示：

在此处输入图像描述

DECLARE @DATENOW datetime
SET @DATENOW = Convert(DateTime, DATEDIFF(DAY, 0, GETDATE()))
use ews
Select CASE
    WHEN ot.PaymentType=0 
    THEN 'Transactional - '
    ELSE 'Subscriptional - '
    End as 'Payment Type',
          count (distinct o.OrderNumber) as 'Count' from    [order] AS o WITH (NOLOCK) LEFT OUTER JOIN
      OrderProducts AS op WITH (NOLOCK) ON o.OrderNumber = op.OrderNumber LEFT OUTER JOIN
      OrderTransaction AS ot WITH (NOLOCK) ON o.OrderNumber = ot.OrderNumber
    where  o.Datecreated >=DATEADD(D,-1,@DATENOW ) 
    AND o.Datecreated <=DATEADD(ms,-3,@DATENOW )
    AND o.status not like'%Deleted%'
    group  by ot.PaymentType
    UNION
     SELECT 'Deleted', count(*)
     from   [order] AS o WITH (NOLOCK) LEFT OUTER JOIN
                    OrderProducts AS op WITH (NOLOCK) ON o.OrderNumber = op.OrderNumber LEFT OUTER JOIN
                    OrderTransaction AS ot WITH (NOLOCK) ON o.OrderNumber = ot.OrderNumber
                   where  o.Datecreated >=DATEADD(D,-1,@DATENOW ) 
    AND o.Datecreated <=DATEADD(ms,-3,@DATENOW )
    and o.status like'%Deleted%'
     group  by ot.paymenttype

在此处输入图像描述

这是原始结果，它显示事务性 STD 和 WB 在第二个结果中求和，但订阅 SCR 和 SSR 没有。我希望将第二个结果中的订阅 SSR 和 SCR 相加。

即
订阅 - 6
交易 - 26

标签： sqlsql-serversql-server-2008

解决方案

我将原始查询更改为子查询，并在子查询之后将组移动。修复列 CountOrder 的总和。

SELECT [Payment Type], SUM([CountOrder]) FROM (
SELECT CASE 
         WHEN ot.paymenttype = 0 THEN 'Transactional - ' 
         ELSE 'Subscriptional - ' 
       END                            AS [Payment Type], 
       Count (DISTINCT o.ordernumber) AS [CountOrder]
FROM   [order] AS o WITH (nolock) 
       LEFT OUTER JOIN orderproducts AS op WITH (nolock) 
                    ON o.ordernumber = op.ordernumber 
       LEFT OUTER JOIN ordertransaction AS ot WITH (nolock) 
                    ON o.ordernumber = ot.ordernumber 
WHERE  o.datecreated >= Dateadd(d, -1, @DATENOW) 
       AND o.datecreated <= Dateadd(ms, -3, @DATENOW) 
       AND o.status NOT LIKE'%Deleted%' 
UNION 
SELECT 'Deleted' AS [Payment Type], 
       Count(*) AS [CountOrder]
FROM   [order] AS o WITH (nolock) 
       LEFT OUTER JOIN orderproducts AS op WITH (nolock) 
                    ON o.ordernumber = op.ordernumber 
       LEFT OUTER JOIN ordertransaction AS ot WITH (nolock) 
                    ON o.ordernumber = ot.ordernumber 
WHERE  o.datecreated >= Dateadd(d, -1, @DATENOW) 
       AND o.datecreated <= Dateadd(ms, -3, @DATENOW) 
       AND o.status LIKE'%Deleted%' 
       )

GROUP BY [Payment Type]

推荐阅读