CODEWITHSUNDEEP
mysqlsql
Lloyd Banks
Lloyd Banks

Reputation: 36659

MySQL - Get Aggregates For Last 1 Day, 7 Days, 30 Days And Allow For Records To Be Counted In More Than 1 Group

I have a table with the following data:

enter image description here

I am looking to group the rows into the following:

The end result for the above rows would look something like:

enter image description here

I can group the records into these brackets right now with:

SELECT (CASE WHEN created_at = CURDATE() THEN '1 Day'
             WHEN created_at >= CURDATE() - INTERVAL 6 DAY THEN '7 Days'
             WHEN created_at >= CURDATE() - INTERVAL 29 DAY THEN '30 Days'
        END) AS Timeframe, COUNT(*) AS Count
FROM my_table
GROUP BY (CASE WHEN created_at = CURDATE() THEN '1 Day'
               WHEN created_at >= CURDATE() - INTERVAL 6 DAY THEN '7 Days'
               WHEN created_at >= CURDATE() - INTERVAL 29 DAY THEN'30 Days'
          END)

But this will prevent individual records from being counted more than once. For example, lines 2 and 3 in the first picture needs to be counted in all three brackets (1 day, 7 days, and 30 days) - while lines 6 through 9 only needs to be counted in the 30 days bracket.

How would you do this with MySQL?

Upvotes: 1

Views: 1623

Answers (2)

joanolo
joanolo

Reputation: 6328

If you want your response in several rows, instead of just one with several columns, take @Gordon Linoff as your starting point... but perform the queries "one row at at time" (it won't be as efficient, because you visit the table 4 times instead of 1!):

-- Row for the 1 day timeframe
SELECT '1 Day' AS `Timeframe`, SUM(created_at = CURDATE()) AS `Count`
FROM my_table

UNION

-- Row for the 7 days timeframe...
SELECT '7 Days' AS `Timeframe`, SUM(created_at >= CURDATE() - INTERVAL 6 DAY) AS `Count`
FROM my_table

UNION

SELECT '30 Days' AS `Timeframe`, SUM(created_at >= CURDATE() - INTERVAL 29 DAY) AS `Count`
FROM my_table

UNION

SELECT 'Older' AS `Timeframe`, SUM(created_at < CURDATE() - INTERVAL 29 DAY) AS `Count`
FROM my_table ;

If you can use MariaDB instead of MySQL, you can use a WITH, which will allow the query to be efficient again:

WITH stats AS
(
SELECT SUM(created_at =  CURDATE())                   as today,
       SUM(created_at >= CURDATE() - INTERVAL  6 DAY) as last_7_days,
       SUM(created_at >= CURDATE() - INTERVAL 29 DAY) as last_30_days,
       SUM(created_at <  CURDATE() - INTERVAL 29 DAY) as older
FROM my_table
)
-- Convert to rows with negligible overhead
SELECT '1 Day'   AS `Timeframe`, today FROM stats
UNION
SELECT '7 Days',  last_7_days          FROM stats
UNION
SELECT '30 Days', last_30_days         FROM stats
UNION
SELECT 'Older',   older                FROM stats ;

In both cases, you'll get (as of 2017-07-25):

Timeframe | today
:-------- | ----:
1 Day     |     0
7 Days    |     4
30 Days   |     8
Older     |     0

dbfiddle here

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270513

It is easiest to do this as columns, rather than rows:

SELECT SUM(created_at = CURDATE()) as today
       SUM(created_at >= CURDATE() - INTERVAL 6 DAY) as last_7_days,
       SUM(created_at >= CURDATE() - INTERVAL 29 DAY) as last_30_days,
       SUM(created_at < CURDATE() - INTERVAL 29 DAY) as older
FROM my_table;

Upvotes: 1

Related Questions