mysqldistinct

MySQL sum a column based on another distinct column count


I need to get sum of hits based on distinct ip

So far, this is what I've come up with:

SELECT 
COUNT(DISTINCT `ip`) AS `total`, 
SUM(`hits`) AS `sum` 
FROM `stats`.`stats` 
WHERE `category`=? 
GROUP BY `date`

Sample data:

|  category |  ip   |  hits  |  date   |
|  11       |  ip1  |  1000  |  date1  |
|  11       |  ip2  |  1000  |  date1  |
|  11       |  ip3  |  1000  |  date1  |
|  11       |  ip4  |  1000  |  date1  |
|  11       |  ip1  |  1000  |  date1  |

Expected results:

ip=4
sum=4000

I am getting as

ip=4
sum=5000

But this is giving total ip hits instead if summing only distinct ip hits.

Please suggest a way to do this. I would prefer doing something like

SUM(CASE WHEN `ip` THAN `hits`)
//or
SUM(CASE WHEN IN(`ip`) THAN `hits`)
//or
SUM(CASE WHEN IF(`ip`) THAN `hits`)

instead of subquery as I need fast query. Also using array_unique PHP side will be good for me.


Solution

  • You can do it like this:

    SELECT count(s.ip) as cntIp,sum(s.hits) as sumHits 
    FROM(
        SELECT DISTINCT t.ip,t.hits
        FROM YourTable t) s
    

    But it looks like the data you provided is not accurate, I see that you group by date, which means that the date equals and not like in your example date1,date2,date3...

    So:

    SELECT s.date,count(s.ip) as cntIp,sum(s.hits) as sumHits 
    FROM(
        SELECT DISTINCT t.ip,t.hits,t.date
        FROM YourTable t) s
    GROUP BY s.date
    

    EDIT:

    SELECT s.date,count(s.ip) as cntIp,sum(s.hits) as sumHits 
    FROM(
        SELECT t.ip,t.hits,t.date
        FROM YourTable t
        GROUP BY t.ip,t.hits,t.date) s
    GROUP BY s.date