Re: 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 <jason@darkpenguin.com> 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" <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 > > > > > > > __________________________________________________ Do You Yahoo!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.com