cybermonkey cybermonkey -4 years ago 77
MySQL Question

How can I ignore duplicate values in another column when using GROUP?

I have the following query:

SELECT
DATE(`timeStamp`),COUNT(*)
FROM
`wf`.sh`
WHERE
(DATE(`timeStamp`) >= curdate()- INTERVAL 31 DAY)
GROUP BY
DATE(`timeStamp`)
HAVING
COUNT(DATE(`timeStamp`)) > 0
ORDER BY
DATE(`timeStamp`) ASC;


The purpose of this query is to retrieve the amount of users online in my system per day, in the space of a month.

Example dataset:

uID timeStamp
1 2016-11-28 00:27:01
1 2016-11-28 01:10:15
1234 2016-11-28 02:50:00
2 2016-11-28 06:11:09
47 2016-11-28 08:32:48
1246 2016-11-28 09:51:47


In its current format, this query returns the count of rows with duplicate dates, for example:

timeStamp COUNT(*)
2017-01-29 256
2017-01-30 224
2017-01-31 240
2017-02-01 95
2017-02-02 136


I have another field
uID
; I need to modify my query so that
GROUP
also ignores rows with a duplicate
uID
field for each day. I tried creating another
GROUP BY
but was given an error that 'incorrect GROUP BY clause' (or something of that nature).

Can this be done via pure MySQL?

Answer Source

You can use a subselect

SELECT 
    visitDate,COUNT(*)  
FROM 
    (SELECT DISTINCT DATE(`timeStamp`) as visitDate, uID FROM `wf`.sh`) alias_t
WHERE 
    (visitDate >= curdate()- INTERVAL 31 DAY)   
GROUP BY 
    visitDate
HAVING 
    COUNT(visitDate) > 0  
ORDER BY 
    visitDate ASC;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download