Oracle SQL – How to group by a column and count other column for each different value?

All we need is an easy explanation of the problem, so here it is.

I’m struggling trying to get the desired result when querying 2 tables.
These are the tables:

Table1

column1 (fk) column2 other_columns…
2 a
2 a
3 c
4 a
4 b
4 b
4 d

Table2

column1 (pk) column2 other_columns…
1 01
2 02
3 03
4 04
(more ids) more…

What I’m trying to accomplish here is creating a view for grouping by table2-column2 and counting the occurrence of a, b, c and d (every different data in table1-column2) for every row of the grouped column; something like this:

Desired view

table2-column2 (grouped-by) a (count) b (count) c (count) d (count)
01 0 0 0 0
02 2 0 0 0
03 0 0 1 0
04 1 2 0 1
more… count…

There is a one2many relation between the tables: table1 can have multiple entries with the same foreign key; so table1-column2 can have multiple duplicated entries for the same data in the column, as shown. All different values in table1-column2 are known values, so where clauses can be used.

I’m having a hard time trying to count the occurrences of every different data in table1-column2 for a specific entry in the grouped-by column (I’m getting the whole amount of entries in table1 for every type of data in table2-column2).

I also tried to create selects for every type of known data in table2-column2, but I didn’t know how to return only one row in that situation, so error single-row subquery returns more than one row was triggered.

Any suggestion is very well appreciated.
Thanks.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

It looks like you want something like this

with t1 as (
  select 2 c1, 'a' c2 from dual union all
  select 2, 'a' from dual union all
  select 3, 'c' from dual union all
  select 4, 'a' from dual union all
  select 4, 'b' from dual union all
  select 4, 'b' from dual union all
  select 4, 'd' from dual
),
t2 as (
  select 1 c1, '01' c2 from dual union all
  select 2, '02' from dual union all
  select 3, '03' from dual union all
  select 4, '04' from dual)
select t2.c2,
       sum( case when t1.c2 = 'a' then 1 else 0 end) count_a,
       sum( case when t1.c2 = 'b' then 1 else 0 end) count_b,
       sum( case when t1.c2 = 'c' then 1 else 0 end) count_c,
       sum( case when t1.c2 = 'd' then 1 else 0 end) count_d
  from t2
       left outer join t1 on (t1.c1 = t2.c2)
 group by t2.c2
 order by t2.c2

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply