具有默认值的oracle sql聚合函数 [英] oracle sql aggregate function with default values

问题描述

我有一个表如下:我想获得每个 id 的 yes 和 NO 的计数.

I have a table as follows: I want to get the count for yes and NO for each id.

counterTb:

id  taxPin  Status
1   123     Y
1   234     Y
1   345     Y
1   456     N
1   567     N
2   678     Y
2   789     Y
2   890     null
2   901     N
1   100     null

对于选定的 id，我的输出应该如下所示:比如 example id = 1;

My output should like like below for a selected id: say example id = 1;

输出

id Status   count
1   YES     3
1   NO      2

如果没有值，说 id = 3

If there are no values , say for id = 3

   id Status    count
    3   YES     0
    3   NO      0

推荐答案

看来这正是您所需要的.注意助手"内联视图，将Y"和N"值创建为一个单独的小表，以及分区外连接(按id分区)) 以确保输入表中存在的每个 id 出现在输出中，Y 和 N 计数.请注意我放在 with 子句中的其他测试用例(严格用于测试，它不是解决方案的一部分 - 删除它并将查询应用于现有表，使用实际表和列名).尤其是 id = 3 需要仔细考虑 - 在编写查询的其他方式中，id 根本不会出现在输出中.

It seems this is what you need. Notice the "helper" inline view, creating the 'Y' and 'N' values as a separate, small table, and the partitioned outer join (partitioned by id) to ensure that every id present in the input table appears in the output, with both the Y and the N counts. Note the additional test cases I put in the with clause (which is there strictly for testing, it is not part of the solution - remove it and apply the query to your existing table, using the actual table and column names). Especially id = 3 needs to be considered carefully - in other ways of writing the query, that id will not appear at all in the output.

with
  counterTb (id, taxPin, Status) as (
    select 1, 123, 'Y'  from dual union all
    select 1, 234, 'Y'  from dual union all
    select 1, 345, 'Y'  from dual union all
    select 1, 456, 'N'  from dual union all
    select 1, 567, 'N'  from dual union all
    select 2, 678, 'Y'  from dual union all
    select 2, 789, 'Y'  from dual union all
    select 2, 890, null from dual union all
    select 2, 901, 'N'  from dual union all
    select 1, 100, null from dual union all
    select 3, 323, null from dual union all
    select 4, 422, 'N'  from dual union all
    select 4, 433, 'N'  from dual
  )
select c.id, h.status, count(c.status) as yn_count
from   (select 'Y' as status from dual union all select 'N' from dual) h
           left outer join
       counterTb c
           partition by (c.id)
           on h.status = c.status
group  by c.id, h.status
having h.status in ('Y', 'N')
order  by id, status desc
;

输出:

ID STATUS YN_COUNT
-- ------ --------
 1 Y             3
 1 N             2
 2 Y             2
 2 N             1
 3 Y             0
 3 N             0
 4 Y             0
 4 N             2

这篇关于具有默认值的oracle sql聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！