-1

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 :)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
test
  • 51
  • 5

2 Answers2

1

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.

markalex
  • 8,623
  • 2
  • 7
  • 32
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

SelVazi
  • 10,028
  • 2
  • 13
  • 29