stack stack - 3 months ago 7
SQL Question

Grouping rows based on some logic?

Here is my table structure:

// reputations
+----+-------+---------------+
| id | value | date_time |
+----+-------+---------------+
| 1 | 5 | 1472105459 | -- in last month
| 2 | -2 | 1472105460 | -- in last month
| 3 | 15 | 1472746410 | -- in last week
| 4 | 5 | 1472746421 | -- in last week
| 5 | 10 | 1472746432 | -- in last week
| 6 | -6 | 1473115566 | -- yesterday
| 7 | 2 | 1473205567 | -- today
| 8 | 10 | 1473205590 | -- today
+----+-------+---------------+


I need to sum
value
column and group them based on these logic:

(CASE WHEN FROM_UNIXTIME(MAX(date_time)) >= CURDATE() THEN 'today'
WHEN FROM_UNIXTIME(MAX(date_time)) >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN 'yesterday'
WHEN FROM_UNIXTIME(MAX(date_time)) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN 'in last week'
ELSE 'in last month'
END) as range_day


So this is expected results:

+----+-------+---------------+
| id | value | date_time |
+----+-------+---------------+
| 2 | 3 | 1472105460 | -- in last month
| 5 | 30 | 1472746432 | -- in last week
| 6 | -6 | 1473115566 | -- yesterday
| 8 | 12 | 1473205590 | -- today
+----+-------+---------------+


Ok well, how can I do that?

Answer

I think you have overcomplicated that. Just get rid of MAX() in you CASE statement. Then you can GROUP BY range_day.

SELECT MAX(id) as id,
  SUM(value) as value,
  (CASE WHEN FROM_UNIXTIME(date_time) >= CURDATE() THEN 'today'
        WHEN FROM_UNIXTIME(date_time) >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN 'yesterday'
        WHEN FROM_UNIXTIME(date_time) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN 'in last week'
        ELSE 'in last month'
  END) as range_day
FROM reputations
GROUP BY range_day

http://sqlfiddle.com/#!9/39e58c/1

Right now SQLFidde returns this:

| id | value |     range_day |
|----|-------|---------------|
|  2 |     3 | in last month |
|  6 |    24 |  in last week |
|  8 |    12 |     yesterday |

That is not equal to your expected result. But that's because time is not a constant. Tomorow you will get another result.

Update:

You are trying to explain your issue using a not working and not reasonable code. But looking at your expected result, i think you want to group by specific date ranges. So let's first convert your table:

SELECT id,
  value,
  DATE(FROM_UNIXTIME(date_time)) as dt,
  CURDATE() as today,
  (CASE WHEN FROM_UNIXTIME(date_time) >= CURDATE() THEN 'today'
        WHEN FROM_UNIXTIME(date_time) >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN 'yesterday'
        WHEN FROM_UNIXTIME(date_time) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN 'in last week'
        ELSE 'in last month'
  END) as range_day
FROM reputations

| id | value |                 dt |              today |     range_day |
|----|-------|--------------------|--------------------|---------------|
|  1 |     5 |    August, 25 2016 | September, 07 2016 | in last month |
|  2 |    -2 |    August, 25 2016 | September, 07 2016 | in last month |
|  3 |    15 | September, 01 2016 | September, 07 2016 |  in last week |
|  4 |     5 | September, 01 2016 | September, 07 2016 |  in last week |
|  5 |    10 | September, 01 2016 | September, 07 2016 |  in last week |
|  6 |    -6 | September, 05 2016 | September, 07 2016 |  in last week |
|  7 |     2 | September, 06 2016 | September, 07 2016 |     yesterday |
|  8 |    10 | September, 06 2016 | September, 07 2016 |     yesterday |

Now let's group the result by "hand":

  • Group "yesterday" has ids 8,7 and valaues 2,10 => max(id) = max(7,8) = 8 and sum(value) = sum(2,10) = 12
  • Group "in last week" has ids 3,4,5,6 and valaues 15,5,10,-6 => max(id) = max(3,4,5,6) = 6 and sum(value) = sum(15,5,10,-6) = 24
  • Group "in last month" has ids 1,2 and valaues 5,-2 => max(id) = max(1,2) = 2 and sum(value) = sum(5,-2) = 3

If you want the "biggest date_time" for each group, just add FROM_UNIXTIME((MAX(date_time)) to your SELECT clause.