How to sum up the days according to a period?

Asked

Viewed 101 times

0

How to sum up days according to a period?

Example: 

+--------------+--------------+--------+----------+
| data_ini     | data_fim     | codfun | qtd_dias |
+--------------+--------------+--------+----------+
| '2014-01-15' | '2014-01-31' | 1      | 16       |
| '2014-02-01' | '2014-02-28' | 1      | 27       |
| '2014-03-01' | '2014-03-22' | 1      | 21       |
| '2014-03-10' | '2014-03-31' | 2      | 21       |
| '2014-04-01' | '2014-04-30' | 2      | 29       |
| '2014-05-01' | '2014-05-12' | 2      | 11       |
| '2014-04-08' | '2014-04-30' | 3      | 22       |
| '2014-05-01' | '2014-05-31' | 3      | 30       |
| '2014-05-01' | '2014-06-19' | 3      | 49       |
+--------------+--------------+--------+----------+

I need to total the column qtd_dias for codfun and bring the initial and final period (earliest date on data_ini and most recent date on data_fim), for example:

+--------+--------------+--------------+----------------+
| codfun | data_ini     | data_fim     | qtd_dias_total |
+--------+--------------+--------------+----------------+
| 1      | '2014-01-15' | '2014-03-22' | 66             |
| 2      | '2014-03-10' | '2014-05-12' | 63             |
| 3      | '2014-04-08' | '2014-06-19' | 72             |
+--------+--------------+--------------+----------------+
mysql sql select-sql

  • Amigo @mcardoso, could you give some more information about the logic of grouping, or some preamble(site)? I didn’t understand very well.

  • The first table is identical as it is in the database and the second (which is the result), would be the branch of the first, ie, should be put the start date and end month to month, according to the period of the first table, result in this second.

  • Your sums in the representative data are wrong, would it be intentional? The @luigibertaco solution works correctly on Mysql.

  • The Restructuring of the Stackoverflow Adm question is wrong! it’s the opposite! I need the detail and not the grouping.

1 answer

2

I believe in your case it would be something like:

select codfun, min(data_ini), max(data_fim), sum(qtd_dias) from TABELA group by codfun;

I’m not sure how this will behave in mysql, but I believe there is no difference with other banks.

Browser other questions tagged mysql sql select-sql

You are not signed in. Login or sign up in order to post.