## 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