Question 20151 in Mysql Question posted in
The official MySQL documentation can be found here.

Grouping and sum up all values per day, month and year in SQL – Mysql

I have a table like this:

"time","value"
"2023-01-28 01:00:03.520","741"
"2023-01-28 14:14:30.520","509"
"2023-01-28 14:16:07.238","584"
"2023-01-28 14:17:49.630","562"
"2023-01-28 14:19:28.024","572"
"2023-01-28 14:21:10.336","569"
"2023-01-28 14:22:52.757","577"
"2023-01-28 14:24:29.733","583"
"2023-01-28 14:26:12.163","581"
"2023-01-28 14:27:50.475","576"
"2023-01-28 14:29:32.869","578"
"2023-01-28 14:31:15.612","580"
"2023-01-28 14:34:35.110","588"
"2023-01-28 14:36:16.407","598"
"2023-01-28 14:37:58.818","613"
"2023-01-28 14:39:35.873","642"
"2023-01-28 14:43:01.070","653"
"2023-01-28 14:46:21.825","662"
"2023-01-28 14:47:58.777","663"
"2023-01-28 14:49:41.514","664"
"2023-01-28 14:51:23.907","666"
"2023-01-28 14:53:00.869","667"
"2023-01-28 14:54:46.089","663"

Now I want to sum up all values which are saved ion one day and display them in a group in SQL.

Here is my attempt:

SELECT 
    MONTH(time), SUM(VALUE) 
FROM 
    item0364 
GROUP BY
    MONTH(time)

but it doesn’t do as expected.

Any help appreciated 🙂

Tags:

2

Answers


  1. 0

    To group by YEAR use YEAR() function or DATE_FORMAT(time, '%Y') :

    Select YEAR(time), SUM(VALUE) 
FROM item0364 
group by YEAR(time)

    To group by MONTH use MONTH() function or DATE_FORMAT(time, '%Y-%m') :

    Select MONTH(time), SUM(VALUE) 
FROM item0364 
group by MONTH(time)

    To group by day then use DATE_FORMAT()

    Select DATE_FORMAT(time, '%Y-%m-%d'), SUM(VALUE) 
FROM item0364 
group by DATE_FORMAT(time, '%Y-%m-%d')

    Demo here

    Login or Signup to reply.
  2. 0

    You can use group by ... with rollup to get all results with one query:

    Select YEAR(time), MONTH(time),  DATE_FORMAT(time, '%Y-%m-%d'), SUM(VALUE) 
FROM item0364 
group by YEAR(time),MONTH(time), DATE_FORMAT(time, '%Y-%m-%d') WITH ROLLUP

    Demo here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search