That is SPOT on, takes 2seconds to process but thats still better than 5 :) -- Dave
----- Original Message ----- From: "Michael Stassen" <[EMAIL PROTECTED]> To: "David Scott" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, February 20, 2004 5:30 PM Subject: Re: SUM() the results of a COUNT() > > > David Scott wrote: > > Hiya peeps... > > > > If i have a table like this: > > +----+--------+ > > | id | uid | > > +----+--------+ > > | 1 | 26 | > > | 2 | 26 | > > | 3 | 267 | > > | 4 | 267 | > > | 5 | 269 | > > | 6 | 269 | > > | 7 | 271 | > > | 8 | 271 | > > | 9 | 271 | > > | 10 | 424 | > > +----+--------+ > > > > I could do this to get the count of uid's: > > SELECT count(*) as C from table GROUP BY uid > > +----+--------+ > > | C | uid | > > +----+--------+ > > | 2 | 26 | > > | 2 | 267 | > > | 2 | 269 | > > | 3 | 271 | > > | 1 | 424 | > > +----+--------+ > > > > But how would i get something like this? > > +----+--------+ > > | A | B | > > +----+--------+ > > | 5 | 10 | > > +----+--------+ > > > > Where A = the number of unique UID's (the number of rows from table > > 2) and B = the SUM of C (derived from "SELECT count(*) as C from table > > GROUP BY uid") > > > > I have been googling for over 1 hour now, looked through the MySQL > > manual, searched the forum archives, please someone put me out of my > > misery... > > > > PS: > > I could use code and loop through the results adding up as I go along, but the real table has over 19,000 records and looping through that many records in ASP takes around 5 seconds. > > > > Cheers > > Dave > > Perhaps I'm misunderstanding what you need, but wouldn't > > SELECT COUNT(DISTINCT(uid)) AS A, COUNT(uid) AS B FROM table; > > give the result you want? > > Michael > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
