CODEWITHSUNDEEP
mysqlmariadb
Matheus Neves
Matheus Neves

Reputation: 75

Sum values while the column value is the same

I am trying to build a timeline. My table have a type column, date_start and date_end, like so:

+------+---------------------+---------------------+----------+
| Type |        Start        |         End         |   Diff   |
+------+---------------------+---------------------+----------+
|    1 | 2020-11-23 23:40:00 | 2020-11-23 23:41:00 | 00:01:00 |
|    1 | 2020-11-23 23:42:00 | 2020-11-23 23:43:00 | 00:01:00 |
|    1 | 2020-11-23 23:44:00 | 2020-11-23 23:45:00 | 00:01:00 |
|    2 | 2020-11-23 23:46:00 | 2020-11-23 23:47:00 | 00:01:00 |
|    2 | 2020-11-23 23:48:00 | 2020-11-23 23:49:00 | 00:01:00 |
|    1 | 2020-11-23 23:50:00 | 2020-11-23 23:51:00 | 00:01:00 |
|    1 | 2020-11-23 23:52:00 | 2020-11-23 23:53:00 | 00:01:00 |
+------+---------------------+---------------------+----------+

I need to sum the differences, while the column value stays the same as the one before. Once the type column value changes, it creates a new line, giving a result like this:

+------+----------+
| Type |   Diff   |
+------+----------+
|    1 | 00:03:00 |
|    2 | 00:02:00 |
|    1 | 00:02:00 |
+------+----------+

How can I achieve such grouping and sum result in MySQL? PS: Don't bother with time logics, if you want to setup an example using integer is perfectly ok.

Upvotes: 1

Views: 93

Answers (2)

P.Salmon
P.Salmon

Reputation: 17665

Use a variable to assign a block number and then aggregate

drop table if exists t;
create table t
( Type int,        Startdt datetime,  Enddt datetime,   Diff time);
insert into t values
(    1 ,'2020-11-23 23:40:00' ,'2020-11-23 23:41:00' , '00:01:00' ),
(    1 ,'2020-11-23 23:42:00' ,'2020-11-23 23:43:00' , '00:01:00' ),
(    1 ,'2020-11-23 23:44:00' ,'2020-11-23 23:45:00' , '00:01:00' ),
(    2 ,'2020-11-23 23:46:00' ,'2020-11-23 23:47:00' , '00:01:00' ),
(    2 ,'2020-11-23 23:48:00' ,'2020-11-23 23:49:00' , '00:01:00' ),
(    1 ,'2020-11-23 23:50:00' ,'2020-11-23 23:51:00' , '00:01:00' ),
(    1 ,'2020-11-23 23:52:00' ,'2020-11-23 23:53:00' , '00:01:00' );

    select type,block,sec_to_time(sum(time_to_sec(diff)))
from
(
select t.*,
         if(type <> @p, @b:=@b+1,@b:=@b) block,
         @p:=type p
from t
cross join (select @b:=0,@p:=0) b
order by startdt,type
) s
group by s.block,s.type;

------+-------+-------------------------------------+
| type | block | sec_to_time(sum(time_to_sec(diff))) |
+------+-------+-------------------------------------+
|    1 | 1     | 00:03:00                            |
|    2 | 2     | 00:02:00                            |
|    1 | 3     | 00:02:00                            |
+------+-------+-------------------------------------+
3 rows in set (0.148 sec)

Upvotes: 2

yusuf hayırsever
yusuf hayırsever

Reputation: 701

SELECT t.type,sum(c.dif)
FROM TABLE t JOIN
(SELECT c.type TIMEDIFF(c.start,c.end) 
as dif FROM TABLE c)ta ON ta.type = t.type
group by t.type

Upvotes: 1

Related Questions