DL, I ran the query exactly as you had sent it and ran into the same error where it talked about mixing counts without a group clause. So, I added a quick "group by clientname" and ran it again....
Advertising
One word.... "PERFECTION". I can't thank you enough. This is now a blistering fast query that produces the exact report I was trying to get. Thanks again!! John Hawkins --- DL Neil <[EMAIL PROTECTED]> wrote: > John, > > > > 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 | > > > The first idea (multiple COUNT()s and GROUP BYs) > didn't work, probably because of the way I was > confusing the > use of table aliases with VIEWs. No matter, let's go > to 'plan B'. > > Following my usual 'formula' approach, breaking it > down into smaller problems: > > 1 getting the data out of the clients tbl and grab > all of the status rows > 2 refine the first ratings criteria to a 'count' > 3 do the same for the second ratings criteria > > The first is a trivial join: > > SELECT clientname, clients.ID, ratings.status-2, > ratings.status-3 > FROM clients, ratings > WHERE clients.ID = ratings.clientID > AND (ratings.status = '2' OR ratings.status = > '3') > > Just to be sure of my assumptions (remember I don't > have enough info to be able to debug/test!), please > check > that this is ALL of the data you want included, and > that none other exists/is missing. > > This won't satisfy you - you don't want line after > line of status data, you want that info aggregated. > Taking > status=2 first, we should go from: > > SELECT clientname, clients.ID, ratings.status-2 > FROM clients, ratings > WHERE clients.ID = ratings.clientID > AND ratings.status = '2' > > to: > > SELECT clientname, clients.ID, > count(ratings.status-2) > FROM clients, ratings > WHERE clients.ID = ratings.clientID > AND ratings.status = '2' > GROUP BY clients.ID > - or should that be GROUP BY ratings.clientID? > (they've 'fiddled' with some of the finer points of > GROUP BY > recently) > > Trouble is this falls apart when we add status-3 - > as we discovered earlier. > > Let's review what COUNT() does. SQL groups the rows > according to the WHERE clause and then notes the > number of > rows in the group. Ok, so we can do that too - let's > imagine adding an extra column to the table, and put > a TRUE > into the column if it is relevant to the count, and > FALSE if it is not. Yes, there is an IF() in SQL > (some very > 'meaty' RTFM starts at 6.3 Functions for Use in > SELECT and WHERE Clauses). > > IF( ratings.status = '2', TRUE, FALSE ) > > So now we could simply count the TRUEs to arrive at > the number of status=2 rows. Substitute 1 for TRUE > and 0 for > FALSE, and we can have SQL do that last calc for us, > ie SUM( imaginary-column ). > > SUM( IF( ratings.status = '2', 1, 0 ) ) > > Because there is no GROUP structure, we can > replicate the same for status='3'. > > SELECT clientname, clients.ID, > SUM( IF( ratings.status = '2', 1, 0 ) ) AS > Status-2, > SUM( IF( ratings.status = '3', 1, 0 ) ) AS > Status-3 > FROM clients, ratings > WHERE clients.ID = ratings.clientID > > BTW I am taking from your query the idea that status > is a string not a numeric. > Another BTW is that appropriate indexing will speed > things up considerably if you are operating on > larger > tables! > > Beaten it this time? > > Please advise, > =dn > > __________________________________________________ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
