Oracle如何汇总多个计数字段 [英] Oracle How to sum up multiple count fields

问题描述

我有以下选择语句:

SELECT 1 AS SoftField, 
       count(form_ref) as Asset
 from risk_register 
where risk_category = 'fdbb8c65-cb78-4e9b-bfb7-d96a9d0b01b1' 
  and system_type = 'AR'
UNION
SELECT 2 AS SoftField,
       count(form_ref)as Operational
from risk_register 
where risk_category = '42a476db-0b3d-4375-9eba-5051d3a2507e' 
  and system_type = 'AR'

输出:

Softfield   Asset 
---------   -----
1           1180
2           2927

我如何总结资产，就像这样:

How can i sum up the Asset to be like this:

Softfield   Asset 
---------   -----
1           1180
2           2927
Total       4107

推荐答案

您可以使用

You could use group by rollup to generate a 'superaggregate' row:

-- CTE for your sample data
with risk_register (system_type, risk_category, form_ref) as (
  select 'AR', 'fdbb8c65-cb78-4e9b-bfb7-d96a9d0b01b1', 'x'
  from dual connect by level <= 1180
  union all
  select 'AR', '42a476db-0b3d-4375-9eba-5051d3a2507e', 'x'
  from dual connect by level <= 2927
)
-- actual query
select case risk_category when 'fdbb8c65-cb78-4e9b-bfb7-d96a9d0b01b1' then '1'
                          when '42a476db-0b3d-4375-9eba-5051d3a2507e' then '2'
                          else 'Total' end as softfield,
count(form_ref) as asset
from risk_register
where risk_category in ('fdbb8c65-cb78-4e9b-bfb7-d96a9d0b01b1',
                        '42a476db-0b3d-4375-9eba-5051d3a2507e')
and system_type = 'AR'
group by rollup(risk_category)
order by softfield;

SOFTFIELD      ASSET
--------- ----------
1               1180
2               2927
Total           4107

这是通过一个字符串字段排序的，该字符串字段(仅)可以使用这些值，但是如果您使用的值大于9，那么它将变得有点怪异，并且您需要使其变得更加复杂.

This is ordering by a string field which is (kind of) OK with only these values, but if you had more than 9 then it would get a little weird and you'd need to make that a little bit more complicated.

@NickKrasnov在评论中链接到简化版本，该注释将"softfield"按类别的字母顺序排列.您的原始查询具有与您提供要搜索的值的顺序相匹配的顺序.尚不清楚这是否重要，因为无论如何，软域值似乎有些武断.保持原始订单而不重复值的另一种方法是使用集合(保持Nick对grouping()的使用):

@NickKrasnov linked to a simplified version on a comment, which puts the 'softfield' in alphabetic order of category. Your original query has that matching the order you provided the values to search for. It isn't clear if that matters, as the softfield value seems a bit arbitrary anyway. Another way to keep your original order, without repeating the values, is to use a collection (keeping Nick's use of grouping()):

select case when grouping(coll.risk_category) = 1 then 'Total' 
            else to_char(max(coll.rn)) end as softfield,
  count(rr.form_ref) as asset
from (
  select rownum as rn, column_value as risk_category
  from table(sys.odcivarchar2list('fdbb8c65-cb78-4e9b-bfb7-d96a9d0b01b1',
                                  '42a476db-0b3d-4375-9eba-5051d3a2507e'))
) coll
join risk_register rr
on rr.risk_category = coll.risk_category
where system_type = 'AR'
group by rollup(coll.risk_category)
order by grouping(coll.risk_category), max(coll.rn);

如果顺序无关紧要，或者您想查看更简单的实际类别:

If the order doesn't matter, or if you want to see the actual categories that's even simpler again:

select case when grouping(risk_category) = 1 then 'Total' 
            else risk_category end as softfield,
  count(form_ref) as asset
from risk_register
where risk_category in ('fdbb8c65-cb78-4e9b-bfb7-d96a9d0b01b1',
                        '42a476db-0b3d-4375-9eba-5051d3a2507e')
and system_type = 'AR'
group by rollup(risk_category)
order by grouping(risk_category), risk_category;

甚至

select nvl(risk_category, 'Total') as softfield,
  count(form_ref) as asset
...

这篇关于Oracle如何汇总多个计数字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！