Re: counting multiple columns based on different values

Perhaps the world is just a little more perfect today... Try this: SELECT clients.clientname,clients.ID,count(ratings2.clientID),count(ratings2.client ID) from clients LEFT JOIN ratings as ratings2 on (clients.ID = ratings2.clientID) LEFT JOIN ratings as ratings3 on (clients.ID = ratings3.clientID) WHERE ratings2.status = '2' AND ratings3.status = '3' GROUP BY clients.clientname; That should do the trick. Enjoy!! Jason Cox ----- Original Message ----- From: "John Hawkins" <isawkeyfan@yahoo.com> To: "php-db list" <php-db@lists.php.net> Sent: Wednesday, February 13, 2002 7:11 PM 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 > > >