I see what you mean. The following query will return a result set where the clientID is returned twice, once of each status, with the number of times each status appears. You would have to run through your result array and match the clientID and the status your looking for to get the count.
SELECT client.clientID,result.status,count(*) FROM client LEFT JOIN rating ON (rating.clientID = client.clientID) WHERE rating.status=2 OR r1.status-3 GROUP BY clientID,status; I think this is the only real way to combine those queries in mysql. If mysql fully supported sub-selects then there would be a better way but it doesn't. Jason Cox ----- Original Message ----- From: "John Hawkins" <[EMAIL PROTECTED]> To: "Jason Cox" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, February 13, 2002 9:08 PM Subject: Re: [PHP-DB] counting multiple columns based on different values > Oops. I spoke too soon. > > This didn't quite get it. My results came back with > column 3 and 4 having the exact same number in them > for each record rather than column 3 having the count > of status 3's and column 4 having a count of the > status 4's. > > I've tried messing with the left join syntax trying to > make it happen, but, I'll be honest with you, I've > never used a left join before. > > Thanks again for your help. > > John > > > > --- Jason Cox <[EMAIL PROTECTED]> wrote: > > 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" <[EMAIL PROTECTED]> > > To: "php-db list" <[EMAIL PROTECTED]> > > 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 > > > > > > > > > > > > > > __________________________________________________ > 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
