Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006
    Posts
    5

    Question Unanswered: Group by >2 columns

    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!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    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

  3. #3
    Join Date
    Jan 2006
    Posts
    5

    Thumbs up

    Thanks for your answer!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •