我试图在查询中进行分组,但是每次数据为0时,分组都不显示整个行。我该如何解决?
这是常规查询
select
COUNT(DISTINCT sr.sr_number) AS NEW_CASES
FROM table sr
where
sr.business_unit in('BU1')
and OPENED_DATE < trunc(current_Date)
and OPENED_DATE > trunc(current_date -2)
and sr.status = 'Open'
输出是
NEW_CASES
0
但是当我做一个分组时。整行不见了。
select
COUNT(DISTINCT sr.sr_number) AS NEW_CASES,
sr.business_unit
FROM table sr
where
sr.business_unit in('BU1','BU2','BU3' )
and OPENED_DATE < trunc(current_Date)
and OPENED_DATE > trunc(current_date -2)
and sr.status = 'Open'
group by sr.business_unit
按输出分组是
New_CASES BUSINESS_UNIT
200 BU2
300 BU3
所需的输出:
New_CASES BUSINESS_UNIT
0 BU1
200 BU2
300 BU3
Use a
left join
. In Oracle, this would look likeOne option is to start from a fixed list of values, and then bring the table with a
left join
, like so: