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....

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

Reply via email to