...

View Full Version : Group by troubles



itxtme
03-21-2013, 02:12 AM
I am wanting to use the power of MYSQL to retrieve the data versus PHP counting.

So I have a table that contains two columns

### myDate ### myClient ###
5/10/13--------101
6/10/13--------102
5/10/13--------101
5/10/13--------101
5/10/13--------102
6/10/13--------103
6/10/13--------101
#####################

What I want it to ouput is one copy of each day and count the number of unique clients attached to those days. I have tried using


SELECT myDate, COUNT( date ) FROM sometable GROUP BY myDate, myClient

This is not working, I have tried the DISTINCT function but still cant get it to release the correct data. Any help much appreciated. Based on the example data, in my mind what I should see is

###myDate###count###
5/10/13--------2-------- (a unique value of 101, and 102)
6/10/13--------3--------(a unique value of 101,102 and 103)

Old Pedant
03-21-2013, 05:04 AM
Trivial in MySQL (and SQL Server, not so easy in some other DBs).



SELECT myDate, COUNT( DISTINCT myClient )
FROM tablename
GROUP BY myDate
ORDER BY myDate

itxtme
03-21-2013, 05:17 AM
Trivial, I love trivial! I find that incredible given the power of such a function. By the way your code works perfectly, thank you.

Old Pedant
03-21-2013, 05:40 AM
MySQL has quite a few surprises like that. Things that are quite hard to do in other DBs that it makes easy. Look into GROUP_CONCAT, for example. Amazingly powerful.

On the other hand, it has some holes (especially in how capably it uses indexes) that you can fly 747's through.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum