Re: SQL: how many people have more than 2 cars

Hello,

    I had a similar requirement with books. A great DBA heloped me with my SQL. You should be able to get what you need by tweaking the SQL below. My request was to get all the customers who have purchased more than 1 book. You could use the same query except change the column names and replace the 1 with a 2.

SELECT c.*
FROM Customer c
WHERE EXISTS (SELECT 1 FROM Purchases p WHERE p.CustNum = c.CustNum

      HAVING count(p.CustNum) > 1)

In His Love
scott

"Tweetie Pooh" <tp601553_at_cia.gov> wrote in message news:Xns908F672D4E67BTweetiePooh_at_62.253.162.108...
> "Ben Thomas" <bthomas_at_strider.localdomain> honoured
> comp.databases.oracle.server on Wed 25 Apr 2001 01:58:39p with
> <9c7dq1$2kip$1_at_allthetime.news.cais.net>:
>
> > I ask this question by example:
> >
> > {table1}:
> > person_id
> >
> > {table2}:
> > person_id
> > car_id
> >
> > each person has one entry in {table1} and zero, one or more entries in
> > {table2}.
> >
> > I want to know how may people have more than 2 cars.
> > Seems easy but I am missing something.
> >
> >
> > help?
> > Much thanks in advance.
> >
> > Ben
> >
>
> check out the HAVING, GROUP BY and COUNT() clauses in a SELECT
Received on Wed May 02 2001 - 04:58:39 CDT