CODEWITHSUNDEEP
mysqlsumrows
Tikky
Tikky

Reputation: 1273

Sum of every three rows in MySQL

I have this MySQL table:

| id | datetime           | readings | phase    
| 1  | 2017-09-01 20:38:45| 5        | 1 
| 2  | 2017-09-01 20:38:45| 5        | 2
| 3  | 2017-09-01 20:38:45| 5        | 3
| 4  | 2017-09-01 20:39:28| 4        | 1 
| 5  | 2017-09-01 20:39:28| 3        | 2
| 6  | 2017-09-01 20:39:28| 4        | 3

Those datas comes from energy monitoring, so every reading have a three rows, for first, second, and third phase. Sum or this three readings should by my energy usage in time.

I would like to get the sum of every three rows where each will be for the same date and sum of three phases.

So in this case I would like to receive from MySQL date and sums" (5+5+5), (4+3+4)..

Something like:

| datetime           | readings_sum    
| 2017-09-01 20:38:45|    15       
| 2017-09-01 20:39:28|    11

Upvotes: 2

Views: 352

Answers (1)

P.Salmon
P.Salmon

Reputation: 17590

Since every reading has 3 rows/phases and assuming every phase starts with 1 then you could assign a block number using a variable and then group by to sum the readings. If you want the first or last datetime in a block use min or max.

CREATE TABLE T (id INT, DT datetime, readings INT, phase INT);
INSERT INTO T VALUES    
( 1 ,  '2017-09-01 20:38:45' ,5        , 1), 
( 2 ,  '2017-09-01 20:38:45' ,5        , 2),
( 3 ,  '2017-09-01 20:38:47' ,5        , 3),
( 4 ,  '2017-09-01 20:39:28' ,4        , 1),
( 5 ,  '2017-09-01 20:39:28' ,3        , 2),
( 6 ,  '2017-09-01 20:39:28' ,4        , 3);

    SELECT s.phaseblock,max(S.DT),SUM(S.READINGS) SumReadings
FROM
(
SELECT T.*,
         IF(T.PHASE = 1 , @BN:=@BN+1,@BN:=@BN) AS PHASEBLOCK,
         @P:=T.PHASE AS P
FROM T ,(SELECT @BN:=0,@P:=0) BLOCK
ORDER BY T.ID
) S
GROUP BY S.PHASEBLOCK;

Result

+------------+---------------------+-------------+
| phaseblock | max(S.DT)           | SumReadings |
+------------+---------------------+-------------+
| 1          | 2017-09-01 20:38:47 |          15 |
| 2          | 2017-09-01 20:39:28 |          11 |
+------------+---------------------+-------------+
2 rows in set (0.08 sec)

Upvotes: 1

Related Questions