needing a count field

Hi,

Consider if you will, the following:

"select count(*) from Bookings where ContactID = x"

The ContactID field is a foreign key from a 'Contacts' table. This is
fine when I know an individual ContactID. When I get a set of
ContactIDs thus:

"select ContactID from Contacts where (etc..)"

How would I get the 2nd query to also get a count as in the 1st? I've
simplified these queries a bit so as to make my question clearer (?)..a
'Contact' might have made a booking and appear in the 'Bookings' table.
I need to find certain contacts and get a count of their bookings at
the same time.

Anyone?

tia,
Hughie

On 15.12.2006 11:36, hughie wrote:
> Hi,
>
> Consider if you will, the following:
>
> "select count(*) from Bookings where ContactID = x"
>
> The ContactID field is a foreign key from a 'Contacts' table. This is
> fine when I know an individual ContactID. When I get a set of
> ContactIDs thus:
>
> "select ContactID from Contacts where (etc..)"
>
> How would I get the 2nd query to also get a count as in the 1st? I've
> simplified these queries a bit so as to make my question clearer (?)..a
> 'Contact' might have made a booking and appear in the 'Bookings' table.
> I need to find certain contacts and get a count of their bookings at
> the same time.
>
> Anyone?

Use "GROUP BY ContactID" with "count(*)".

robert

> Use "GROUP BY ContactID" with "count(*)".

thanks. for the record my query ended up as:

select c.ContactID, c.ContactName, count(*) as "bookings"
from Contacts c
left join Members m on
c.ContactID = m.ContactID
join Bookings b on
c.ContactID = b.ContactID
where m.ContactID is null
group by b.ContactID
having bookings > 2
order by bookings