CODEWITHSUNDEEP
sqloracle-database
Lance
Lance

Reputation: 23

nested Group by

I have the following data in a Oracle database:

Name    Place     Color
------- --------- --------
John    Paris     Blue
John    Miami     Blue
Ryan    Boston    Red
Ryan    Boston    Red
Jim     Miami     Blue
Ryan    Oslo      Red
Jason   Rome      Green
Jim     Paris     Blue
Jason   Rome      Green
Jim     Paris     Blue
Ryan    Boston    Red

I need to dedup if two columns are the same(name/place) and then count by a third column. I am trying to group by twice with a nested select but I keep getting an ORA-00933 error. 

select Color, count(Color)
from
(
    select TO_CHAR(Name)||'-'||(Place) as unique_ident from mytable
    group by TO_CHAR(Name)||'-'||(Place)
) as inline
group by Color


I would to return something like 

Blue   4
Red    2
Green  1

Any help is appreciated.

Upvotes: 2

Views: 517

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

The answer by sgeddes is fine. The problem with your query is that your outer query references color, but it is not in your inner query. You can fix this by doing:

select Color, count(Color)
from (select TO_CHAR(Name)||'-'||(Place) as unique_ident, color
      from mytable
      group by TO_CHAR(Name)||'-'||(Place), color
     ) as inline 
group by Color;

However, the count(distinct) formulation is probably preferable.

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

I think you're looking for something like this:

SELECT Color, COUNT(DISTINCT name || '-' || place) ColorCnt
FROM yourtable
GROUP BY Color

Resulting In:

COLOR   ColorCnt
-----------------
Green   1
Blue    4
Red     2

Upvotes: 2

Hogan
Hogan

Reputation: 70523

Doesn't this give you the results you want?

select TO_CHAR(Name)||'-'||(Place) , color, count(*) as color_count
from mytable
group by name, place, color

Upvotes: 0

Related Questions