John, Don't tell me I asked you to do a SUM() without a GROUP BY clause!!!??? Someone pass me a hammer! - guess I was so focused on getting around that incompatible pair of GROUP BYs in the first attempt. It is so much easier when I can 'play' with the data in MySQL-Front to 'debug'!
Rather than ClientName, it might be better to use ID if it is an INDEXed field/PRIMARY KEY, and/or because it is the join column. Perfection is that you figured out where I wanted to take you AND cleared the missing link. Well done! Can't help you with those blisters though - maybe the MySalves and PHP liniments discussion list? =dn > 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.... > > 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 > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
