OraFAQ Forum: SQL & PL/SQL » query based on values

Home » SQL & PL/SQL » SQL & PL/SQL » query based on values

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

query based on values [message #7502]

Wed, 18 June 2003 01:54

Sowmya
Messages: 5
Registered: August 2002

Junior Member

Hi, I have a table in which two of the various columns are City and visiting places.
For eg:
--------------
City Places
--------------
AA 123
AA 345
BB 234
CC 534
CC 8987
DD 435

Now i want to retrieve only those city names which have more than one visiting places. For eg I want only AA and CC in the above said talbe entries. Also I want only the city name with only one visiting places(like BB and DD).
can any one help in providing the query pls

Report message to a moderator

Re: query based on values [message #7503 is a reply to message #7502]

Wed, 18 June 2003 04:40

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Use GROUP BY ... HAVING ...
Look at the example:

SQL> select deptno, count(*)
  2    from emp
  3   group by deptno
  4   having count(*) > &t
  5  /
Enter value for t: 1
old   4:  having count(*) > &t
new   4:  having count(*) > 1

    DEPTNO   COUNT(*)
---------- ----------
        10          3
        20          5
        30          6

SQL> /
Enter value for t: 3
old   4:  having count(*) > &t
new   4:  having count(*) > 3

    DEPTNO   COUNT(*)
---------- ----------
        20          5
        30          6

SQL> /

HTH,
MHE

Report message to a moderator

Previous Topic:	DBMS.JOB SCHEDULE - HELP
Next Topic:	Machine Name

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 17:55:04 CDT 2024