两个查询及其总和的差异

Below are two simple/similar queries that I would like to join into one. I also want to have a column containing the difference between the two sums in either query.

下面是两个简单/类似的查询,我想加入其中一个。我还希望有一个列包含任一查询中两个总和之间的差异。

(I've found help on the join of two queries but I think there is a simpler solution and I am new to SQL)

(我在两个查询的连接上找到了帮助,但我认为有一个更简单的解决方案,我是SQL的新手)

I want my output to be 4 columns:

我希望我的输出为4列:

Post_Date
All_Payments
CCP_Payments
All_Payments - CCP_Payments (SUBTRACT THE TWO)

All_Payments - CCP_Payments(减去两个)

Query 1:

SELECT
TDL.POST_DATE
, SUM(CASE WHEN TDL.DETAIL_TYPE IN(2,5,11,20,22,32,33) THEN TDL.AMOUNT*-1 ELSE 0 END) PAYMENTS

FROM
STG_OJDT.STG_CL.CLARITY_TDL_TRAN TDL

WHERE
TDL.POST_DATE = '2018-08-01 00:00:00'
AND TDL.SERV_AREA_ID = 10

GROUP BY 
TDL.POST_DATE

Query2:

SELECT
TDL.POST_DATE
, SUM(CASE WHEN TDL.DETAIL_TYPE IN(2,5,11,20,22,32,33) THEN TDL.AMOUNT*-1 ELSE 0 END) CCP_PAYMENTS

FROM
STG_OJDT.STG_CL.CLARITY_TDL_TRAN TDL

WHERE
TDL.POST_DATE = '2018-08-01 00:00:00'
AND TDL.SERV_AREA_ID = 10
AND TDL.BILL_AREA_ID IN (810000020, 810000025, 810000030)
GROUP BY 
TDL.POST_DATE

1 个解决方案

#1

You can calculate the two values in a single row using conditional aggregation:

您可以使用条件聚合计算单行中的两个值:

SELECT TDL.POST_DATE,
       SUM(CASE WHEN TDL.DETAIL_TYPE IN (2, 5, 11, 20, 22, 32, 33) THEN TDL.AMOUNT*-1 ELSE 0 END) as PAYMENTS,
       SUM(CASE WHEN TDL.DETAIL_TYPE IN (2, 5, 11, 20, 22, 32, 33) AND TDL.BILL_AREA_ID IN (810000020, 810000025, 810000030) THEN TDL.AMOUNT*-1 ELSE 0 END) as CCP_PAYMENTS
FROM STG_OJDT.STG_CL.CLARITY_TDL_TRAN TDL
WHERE TDL.POST_DATE = '2018-08-01 00:00:00' AND
      TDL.SERV_AREA_ID = 10    
GROUP BY  TDL.POST_DATE;

If you just want the difference, though:

但是,如果你只是想要差异:

SELECT TDL.POST_DATE,
       SUM(CASE WHEN TDL.DETAIL_TYPE IN (2, 5, 11, 20, 22, 32, 33) THEN TDL.AMOUNT*-1 ELSE 0 END) as diff
FROM STG_OJDT.STG_CL.CLARITY_TDL_TRAN TDL
WHERE TDL.POST_DATE = '2018-08-01 00:00:00' AND
      TDL.SERV_AREA_ID = 10 AND 
      TDL.BILL_AREA_ID NOT IN (810000020, 810000025, 810000030) 
GROUP BY TDL.POST_DATE

#1