时间戳

首页 > 解决方案 > 计数以将多个列作为 SQL 查询返回

sql - 计数以将多个列作为 SQL 查询返回

问题描述

对此非常新，但这里有，

我编写了以下内容，以使用以下状态按日期计算所有记录，

Select trunc(create_date_time) as "CREATED DATE", count(*) as Cancelled
from TASK_HDR
where WHSE = 102 and INVN_NEED_TYPE = 101 and STAT_CODE = 99
    and trunc(create_date_time)>= add_months(trunc(sysdate,'mm'),-1)
Group BY trunc(create_date_time)
order by trunc(create_date_time) asc;

Select trunc(create_date_time) as "CREATED DATE", count(*) as Released
from TASK_HDR
where WHSE = 102 and INVN_NEED_TYPE = 101 and STAT_CODE = 10
    and trunc(create_date_time)>= add_months(trunc(sysdate,'mm'),-1)
Group BY trunc(create_date_time)
order by trunc(create_date_time) asc;

我要做的是创建它，以便我并排返回一个查询和结果

Date       Outstanding   Completed
04-JAN-21       1            10
05-JAN-21       2            12
06-JAN-21       7            15
08-JAN-21       1            8
11-JAN-21       4            7
01-FEB-21       3            3
02-FEB-21       6            1
04-FEB-21       6            0

我真的很感激这方面的帮助，

标签： sqlcount

解决方案

使用case表达式进行条件聚合：

Select trunc(create_date_time) as "CREATED DATE",
       sum(case when STAT_CODE = 99 then 1 else 0 end) as Cancelled,
       sum(case when STAT_CODE = 10 then 1 else 0 end) as Released
from TASK_HDR
where WHSE = 102 and INVN_NEED_TYPE = 101 and STAT_CODE in (99, 10)
    and trunc(create_date_time)>= add_months(trunc(sysdate,'mm'),-1)
Group BY trunc(create_date_time)
order by trunc(create_date_time) asc;

推荐阅读