[ad_1]

I think, this example suggests a small nuance on how the partitioning works and how group by works. My example is from Oracle 12, if my example happens to be a compiling bug.

I tried :

```
SELECT t.data_key
, SUM ( CASE when t.state="A" THEN 1 ELSE 0 END)
OVER (PARTITION BY t.data_key) count_a_rows
, SUM ( CASE when t.state="B" THEN 1 ELSE 0 END)
OVER (PARTITION BY t.data_key) count_b_rows
, SUM ( CASE when t.state="C" THEN 1 ELSE 0 END)
OVER (PARTITION BY t.data_key) count_c_rows
, COUNT (1) total_rows
from mytable t
group by t.data_key ---- This does not compile as the compiler feels that t.state isn't in the group by and doesn't recognize the aggregation I'm looking for
```

This however works as expected :

```
SELECT distinct t.data_key
, SUM ( CASE when t.state="A" THEN 1 ELSE 0 END)
OVER (PARTITION BY t.data_key) count_a_rows
, SUM ( CASE when t.state="B" THEN 1 ELSE 0 END)
OVER (PARTITION BY t.data_key) count_b_rows
, SUM ( CASE when t.state="C" THEN 1 ELSE 0 END)
OVER (PARTITION BY t.data_key) count_c_rows
, COUNT (1) total_rows
from mytable t;
```

Producing the number of elements in each state based on the external key “data_key”. So, if, data_key = ‘APPLE’ had 3 rows with state ‘A’, 2 rows with state ‘B’, a row with state ‘C’, the corresponding row for ‘APPLE’ would be ‘APPLE’, 3, 2, 1, 6.

[ad_2]