Oracle sql to count instances of different values in single column

Question

I have a table with status column. I want an Oracle sql query which will list me count of rows in each status in only one row. for eg if my table is

Table A
Id       Status  Fkey
1         20      500
2         20      500  
3         30      501
4         40      501
5         30      502

Output should be

Fkey     Count_status20     Count_status30    Count_status40
500        2                      0                 0
501        0                      1                 1

A slight twist here

Table B 
FKey TKey 
500   1001 
501   1001
502   1002

Now Output should be

TKey Count_status20     Count_status30    Count_status40 
1001     2                     1                    1 
1002     0                     1                    0

I want to so something like for col in ('20' as Count_Status20, '30' or '40' as Count_Status30, '50' as Count_Status50) I get syntax error when trying to do. How is it possible to aggregate columns ? — Thunderhashy, Nov 06 '12 at 20:31
@Harsha ... my suggestion is to re-accept the answer and ask a new one with your new requirements. — swasheck, Nov 06 '12 at 20:33
Can you post a new question explaining with sample data, what you want for a final result? It is not clear what you are trying to do. — Taryn, Nov 06 '12 at 20:33
Please do not modify an existing question like this. If you had a question with a working answer, leave it and open a new question about the issue. — JNK, Nov 06 '12 at 20:35
Ok I did post a new question here it is http://stackoverflow.com/questions/13258990/oracle-sql-to-count-instances-of-different-values-in-single-column-continuatio — Thunderhashy, Nov 06 '12 at 20:40

Taryn · Accepted Answer · 2012-11-05T18:51:23.753

14

If you are using Oracle 11g, then you can use the PIVOT function:

select *
from
(
  select tkey, status, 
    status as col
  from tableB b
  left join tableA a
    on a.fkey = b.fkey
) src
pivot
(
  count(status)
  for col in ('20' as Count_Status20, 
              '30' as Count_Status30,
              '40' as Count_Status40)
) piv;

See SQL Fiddle with Demo

If you are not using Oracle11g, then you can use an aggregate function with a CASE statement:

select tkey, 
  count(case when status = 20 then 1 else null end) as Count_Status20,
  count(case when status = 30 then 1 else null end) as Count_Status30,
  count(case when status = 40 then 1 else null end) as Count_Status40
from tableB b
left join tableA a
  on b.fkey = a.fkey
group by tkey

See SQL Fiddle with Demo

edited Nov 05 '12 at 18:51

answered Nov 05 '12 at 18:37

Taryn

242,637
56
362
405

@Harsha please see my edit, the code is the same you will just join the tables together – Taryn Nov 05 '12 at 18:51
Using you query I get counts in multiple rows TKey Count_status20 Count_status30 Count_status40 1001 2 0 0 1001 0 1 0 1001 0 0 1 1002 1 0 0 – Thunderhashy Nov 05 '12 at 18:58
In output I want one row per TKey – Thunderhashy Nov 05 '12 at 18:58
@Harsha which version of the query are you using? If you are using the `PIVOT` and if you are including any additional fields in the subquery then the items will not group by correctly. – Taryn Nov 05 '12 at 18:59
@swasheck: my bad but I had a slight twist to the original problem – Thunderhashy Nov 06 '12 at 20:33
I just used the COUNT(case-statement) method, just because it's easy to read and understand, without learning the pivot thing (which I'm not handy with yet). Worked great. – qxotk Oct 28 '15 at 20:07

score 3 · Answer 2 · answered Nov 05 '12 at 18:34

select fkey,
       sum(case when status = 20 then 1 else 0 end) as count_status20,
       sum(case when status = 30 then 1 else 0 end) as count_status30,
       sum(case when status = 40 then 1 else 0 end) as count_status40,
from your_table
group by fkey

Oracle sql to count instances of different values in single column

2 Answers2

Linked

Related