OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » SQL Group By

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Group By [message #23444]

Mon, 09 December 2002 13:57

Yelanda
Messages: 4
Registered: June 2002

Junior Member

Hello, everyone.
I hope someone helps me with this sql.

Let's assume we have this table:

ID GROUP_ID VALUE
1 15 ABC
2 16 EFG
3 16 JGK
4 17 GGT
5 18 MHW
6 18 FFW
7 18 KKQ

I need to create 2 SELECTs: one that would select rows where group_id is selected only once, the other - where group_id is repeated more than once. So, it should look like this.

CASE 1:

ID GROUP_ID VALUE
1 15 ABC
4 17 GGT

CASE 2:
ID GROUP_ID VALUE
2 16 EFG
3 16 JGK
5 18 MHW
6 18 FFW
7 18 KKQ

Any advice?

Report message to a moderator

Re: SQL Group By [message #23445 is a reply to message #23444]

Mon, 09 December 2002 14:05

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

sql>select *
  2    from t
  3   where group_id in  
  4           (select group_id
  5              from t
  6             group by group_id
  7             having count(*) = 1);
 
       ID  GROUP_ID VAL
--------- --------- ---
        1        15 ABC
        4        17 GGT
 
2 rows selected.
 
sql>select *
  2    from t
  3   where group_id in  
  4           (select group_id
  5              from t
  6             group by group_id
  7             having count(*) > 1);
 
       ID  GROUP_ID VAL
--------- --------- ---
        2        16 EFG
        3        16 JGK
        5        18 MHW
        6        18 FFW
        7        18 KKQ
 
5 rows selected.

Report message to a moderator

Previous Topic:	viewing an export file
Next Topic:	Update Creating - Rollback segment problems.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 16 00:54:50 CDT 2024