This should do the trick (untested):

SELECT ClientName, Count(*) FROM Clients LEFT JOIN Ratings ON Clients.ID =
Ratings.ClientID
GROUP BY ClientName
HAVING Ratings.Status = 2 OR Ratings.Status = 3


regards,

Peter
Software Engineer / Trainer
Vervoorn IT
----- Original Message -----
From: "John Hawkins" <[EMAIL PROTECTED]>
To: "php-db list" <[EMAIL PROTECTED]>
Sent: Thursday, February 14, 2002 3:11 AM
Subject: [PHP-DB] counting multiple columns based on different values


> I'm gonna go out on a limb and guess that I'm missing
> something obvious (and easy) because this sure seems
> like it should be able to be done.
>
> Here's the issue: I need to pull the client name and
> ID out of one table and then, count the records in a
> different table (called ratings) that match two
> different criteria.
>
> If I was doing 2 different sql statments, they would
> look like this:
>
> select clientname, clients.ID, count(*) FROM clients,
> ratings WHERE clients.ID = ratings.clientID AND
> ratings.status = '2'
> select clientname, clients.ID, count(*) FROM clients,
> ratings WHERE clients.ID = ratings.clientID AND
> ratings.status = '3'
>
> In a perfect world, I'd be able to receive the
> following data from a single query:
>
>
> | ClientName | ClientID | Status-2 | Status-3|
> | Bob        | 28       | 103      | 87      |
> | Steve      | 29       | 11       | 106     |
> | Jerry      | 30       | 50       | 82      |
>
> I sure hope I explained that well enough.
>
> Thanks!
>
> John
>
> __________________________________________________
> Do You Yahoo!?
> Send FREE Valentine eCards with Yahoo! Greetings!
> http://greetings.yahoo.com
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to