CODEWITHSUNDEEP
sqloracle-database
Geek
Geek

Reputation: 3329

group by along with totals in the same query

I have the below query

select role,  count(*) as cases  from ( select
     CASE WHEN r.id = 30 THEN r.name ELSE r.name || ' ' || u.member_id END AS role
    from case_inventory ci, users u, roles r
    where ci.board_id = u.board_id and
          ci.assigned_to = u.io_id and 
          u.role_id = r.id
          and ci.case_id = 40) 
    group by role;

Output is :

  Role          Cases
  President      1
  Student Member 2

I want the totals in the same query. How should I go forward?

  Role          Cases
  President      1
  Student Member 2  
  Totals         3

Upvotes: 0

Views: 78

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Two things:

  • Never use commas in the FROM clause. Always use proper, explicit, standard, readable JOIN syntax.
  • Qualify all column references in a query that references more than one table.

To answer your question in Oracle, use grouping sets:

select coalesce(role, 'Total') as role, count(*) as cases 
from (select (case when r.id = 30 then r.name else r.name || ' ' || u.member_id
              end_ AS role
    from case_inventory ci join
         users u
         on ci.board_id = u.board_id and
            ci.assigned_to = u.io_id join
         roles r
         on u.role_id = r.id
     where ci.case_id = 40
    ) r
group by grouping sets ( (role), () );

Upvotes: 1

Atif
Atif

Reputation: 2210

You can simply rewrire your query by adding rollup in the query:

select nvl(role, 'Totals') role,  count(*) as cases  from ( select
     CASE WHEN r.id = 30 THEN r.name ELSE r.name || ' ' || u.member_id END AS role
    from case_inventory ci, users u, roles r
    where ci.board_id = u.board_id and
          ci.assigned_to = u.io_id and 
          u.role_id = r.id
          and ci.case_id = 40) 
    group by Rollup(role);

Upvotes: 1

Related Questions