合并2个聚合查询：在单行中显示结果（DB2）_Db2

合并2个聚合查询：在单行中显示结果（DB2）

db2

合并2个聚合查询：在单行中显示结果（DB2）,db2,Db2,第一个问题 SELECT count(*) AS TAXTOTAL FROM T_tx_tax_report rpt INNER JOIN T_TX_TAX_REPORT_TRANSACTION trans ON rpt.TAX_REPORT_ID = trans.TAX_REPORT_ID WHERE rpt.created_ts < '10/01/2015' AND trans.TAX_REPORT_SOURCE NOT IN ('MIGR') GROUP BY

第一个问题

SELECT count(*) AS TAXTOTAL
FROM T_tx_tax_report rpt
  INNER JOIN T_TX_TAX_REPORT_TRANSACTION trans
    ON rpt.TAX_REPORT_ID = trans.TAX_REPORT_ID
WHERE rpt.created_ts < '10/01/2015'
      AND trans.TAX_REPORT_SOURCE NOT IN ('MIGR')
GROUP BY (rpt.created_ts), trans.TAX_REPORT_SOURCE, rpt.TOTAL_WAGES
ORDER BY (rpt.created_ts) DESC, trans.TAX_REPORT_SOURCE ASC

WHERE子句中创建的条件是这两个查询之间的唯一区别。组合查询以在一行中显示结果。谢谢

            No. Prior to             No. On
             10/01/2015               10/1/2015           Total No.      
 a                     5                    1                   6
 b                     10                   3                   13

您只需简单地将一个或另一个条件为真的情况相加，大致如下：

SELECT 
  ...,
  sum(case when rpt.created_ts < '10/01/2015' then 1 end) "No. Prior to date",
  sum(case when rpt.created_ts = '10/01/2015' then 1 end) "No. On date",
  count(*) as "Total No."
FROM    T_tx_tax_report rpt
INNER JOIN T_TX_TAX_REPORT_TRANSACTION trans 
ON rpt.TAX_REPORT_ID =      trans.TAX_REPORT_ID
where   rpt.created_ts <= '10/01/2015'
...

选择
...,
金额（如rpt.created_ts<'10/01/2015'则1结束）“日期前的编号”，
金额（如rpt.created_ts='10/01/2015'则1结束）“日期编号”，
将（*）计为“总数”
来自德克萨斯州税务局报告rpt
内部连接发送税务报告事务传输
在rpt.TAX_REPORT_ID=trans.TAX_REPORT_ID上
rpt.created的位置\u ts mmhh您需要编辑。太可怕了->
SELECT 
  ...,
  sum(case when rpt.created_ts < '10/01/2015' then 1 end) "No. Prior to date",
  sum(case when rpt.created_ts = '10/01/2015' then 1 end) "No. On date",
  count(*) as "Total No."
FROM    T_tx_tax_report rpt
INNER JOIN T_TX_TAX_REPORT_TRANSACTION trans 
ON rpt.TAX_REPORT_ID =      trans.TAX_REPORT_ID
where   rpt.created_ts <= '10/01/2015'
...