martincarlin87 martincarlin87 -4 years ago 92
MySQL Question

MySQL Group Grouped By Result

I have a very simple table which consists of the following columns:

id | customer_id | total | created_at


I was running this query to get the results per day for the last ten days:

SELECT SUM(total) AS total, DATE_FORMAT(created_at, "%d/%m/%Y") AS date
FROM table
WHERE created_at BETWEEN "2017-02-20" AND "2017-03-01"
GROUP BY created_at
ORDER BY created_at DESC


This works fine, but I've just noticed that there's an issue with imported rows being duplicated for some reason so I'd like to update the query to be able to handle the situation if it ever happens again, in other words select one row instead of all when the date and customer id are the same (the total is also identical).

If I add
customer_id
to the group by that seems to work but the trouble with that is then the query returns a result per day for each customer when I only want the overall total.

I've tried a couple of things but I haven't cracked it yet, I think it will be achievable using a sub query and/or an inner join, I have tried this so far but the figures are very wrong:

SELECT
created_at,
(
SELECT SUM(total)
FROM table test
WHERE test.created_at = table.created_at
AND test.customer_id = table.customer_id
GROUP BY customer_id, created_at
LIMIT 1
) AS total
FROM table
WHERE created_at BETWEEN "2017-02-20" AND "2017-03-01"
GROUP BY created_at
ORDER BY created_at DESC


It's also a large table so finding a performant way to do this is also important.

Answer Source

First, are you sure that created_at is a date and not a datetime? This makes a big difference.

You can do what you want using two levels of aggregation:

SELECT SUM(max_total) AS total, DATE_FORMAT(created_at, '%d/%m/%Y') AS date
FROM (SELECT t.customer_id, t.created_at, MAX(total) as max_total
      FROM table t
      WHERE t.created_at BETWEEN '2017-02-20' AND '2017-03-01'
      GROUP BY t.customer_id, t.created_at
     ) t
ORDER BY created_at DESC
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download