All geek questions in one place

MySQL tries to get an hourly summary and rely on row data

MyTable:

tday char(3) dow int(1) tdate date ttime time T0 decimal(5,2) T1 decimal(5,2) T2 decimal(5,2) T3 decimal(5,2) Total decimal(8,2)

Given the above structure and the following data:

 DoW IM CDate CTime G0 G1 G2 G3 Thu 4 2010-12-02 05:29:15 1 3 0 11 Thu 4 2010-12-02 05:34:22 0 4 1 0 Thu 4 2010-12-02 05:39:28 6 0 7 0 Thu 4 2010-12-02 05:44:35 8 7 3 9 Thu 4 2010-12-02 05:49:41 0 7 0 8 Thu 4 2010-12-02 05:54:48 1 7 0 1 Thu 4 2010-12-02 05:59:54 3 0 5 3 Thu 4 2010-12-02 06:05:01 0 9 1 0 Thu 4 2010-12-02 06:10:07 2 9 10 0 Thu 4 2010-12-02 06:15:13 0 0 16 0 Thu 4 2010-12-02 06:20:20 2 0 8 0 Thu 4 2010-12-02 06:25:26 1 0 0 0 Thu 4 2010-12-02 06:30:33 1 5 19 21 Thu 4 2010-12-02 06:35:39 3 0 6 0 Thu 4 2010-12-02 06:40:46 3 4 7 2 Thu 4 2010-12-02 06:45:52 3 3 0 20 Thu 4 2010-12-02 06:50:58 5 0 7 10 Thu 4 2010-12-02 06:56:05 0 7 7 0 Thu 4 2010-12-02 07:01:11 7 0 10 3 Thu 4 2010-12-02 07:06:18 0 0 0 1 Thu 4 2010-12-02 07:11:24 0 0 0 0 Thu 4 2010-12-02 07:16:31 9 3 1 0 Thu 4 2010-12-02 07:21:37 0 0 3 10 Thu 4 2010-12-02 07:26:44 0 1 3 0

What I'm trying to get is the sum of the columns G0..G3 hourly per day.

I tried "Select CTime, sum (G0), sum (G1), sum (G2), sum (G3) from the MyTable group in CTime order by CDate; and various similar statements without any success. The results I'm trying to get following:

 DoW IM CDate CTime G0 G1 G2 G3 Thu 4 2010-12-02 05:00:00 19 28 16 32 Thu 4 2010-12-02 06:00:00 20 51 86 65 Thu 4 2010-12-02 07:00:00 16 4 17 14

Could you help me. I spent a lot of time reviewing already presented solutions and other answers on the Internet, but nothing comes close to what seems to me necessary in a particular SQL query.

Thanks. Graham

+4
source share
2 answers

I think you are looking for something like

 SELECT DoW, IM, CDate, HOUR(CTime) AS recorded_hour, SUM(G0) AS sum_0, SUM(G1) AS sum)1, SUM(G2) AS sum_2, SUM(G3) AS sum_3 FROM MyTable GROUP BY recorded_hour

If you want the hour for different dates to be different, you just need to add the date column to the group as suggested

+2
source

What type of CTime and CDate?
Can your past have a normal table structure in pre & code

Try running this:
Select CDate, HOUR(CTime), sum(G0), sum(G1), sum(G2), sum(G3) from MyTable group by CDate, HOUR(CTime) order by CDate, CTime

CLOCK function

+1
source

Source: https://habr.com/ru/post/1341104/

More articles:


All Articles