Group by >2 columns

gixig · #1 (**permalink**) 05-12-11, 04:03

Hi all,

I'm really stuck with this table:

MAP
--------------------------
ID - CLASS - COUNTRY
1 - B - US
2 - B - CA
3 - A - CA
1 - B - PT
1 - B - CA
1 - C - US
2 - A - CA
2 - A - US
3 - B - CA

I have to query id's with same class and >2 different countries. I've tried :

select id,class,country from map group by id,class,country having count(country)>2;

But doesn't work

. Any advice or clue?

Thanks!

shammat · #2 (**permalink**) 05-12-11, 05:01

Code:

SELECT id,
       class,
       country
FROM (
   SELECT id, 
          class,
          country,
          count(country) over (partition by class) as cntr_count
   FROM your_table
) t
WHERE cntr_count = 2

gixig · #3 (**permalink**) 05-12-11, 05:59

Thanks for your answer!!!!

Internet Services

Web Development

Digital Marketing

Consumer Tech