user2042985 user2042985 - 4 months ago 10
SQL Question

SQL command to sum by day/month

I have the following table:

+----+---------------------+-------------+-----------------+
| id | stat_time | reads | writes |
+----+---------------------+-------------+-----------------+
| 1 | 2013-07-18 20:00:00 | 42614543 | 1342129 |
| 2 | 2013-07-18 21:00:00 | 23085319 | 326139 |
| 3 | 2013-07-25 12:00:00 | 0 | 39639 |
| 4 | 2013-07-25 13:00:00 | 754166 | 39639 |
| 5 | 2013-07-25 14:00:00 | 693382 | 295323 |
| 6 | 2013-07-25 15:00:00 | 1334462 | 0 |
| 7 | 2013-07-25 16:00:00 | 10748367 | 261489 |
| 9 | 2013-07-25 17:00:00 | 4337294 | 0 |
| 10 | 2013-07-25 18:00:00 | 3002796 | 0 |
| 11 | 2013-07-25 20:00:00 | 3002832 | 0 |
| 12 | 2013-07-25 23:00:00 | 0 | 333468 |
| 13 | 2013-07-26 17:00:00 | 10009585 | 0 |
| 15 | 2013-07-26 18:00:00 | 6005752 | 0 |
| 17 | 2013-07-26 21:00:00 | 333663 | 0 |
+----+---------------------+-------------+-----------------+


I would like to perform something like this:

SELECT stat_time, SUM(reads), SUM(writes) from this_table GROUP BY stat_time HAVING 'the same day'..


So a command which outputs three rows (one for 2013-07-18, second one for 2013-07-25 and third one for 2013-07-26) and in every such row in the column reads/writes there is a sum of reads/writes in this day.

Thank you,
David

Answer

You want to convert the stat_time to a day to do this. The method depends on the database. Here is one way:

SELECT cast(stat_time as date) as stat_day, SUM(reads), SUM(writes)
from this_table
GROUP BY cast(stat_time as date)
order by stat_day;

Here are some other approaches (for MySQL and Oracle):

SELECT date(stat_time) as stat_day, SUM(reads), SUM(writes)
from this_table
GROUP BY date(stat_time)
order by stat_day;

SELECT trunc(stat_time) as stat_day, SUM(reads), SUM(writes)
from this_table
GROUP BY trunc(stat_time)
order by stat_day;