PDA

View Full Version : Group by troubles



itxtme
03-21-2013, 03: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, 06: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, 06: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, 06: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.