How to add values of a field grouped by date?

Question

SELECT funcionario_id, data, avaliacao_postura FROM equipe 

id  funcionario_id  data           avaliacao_postura            
1   1               2014-03-02     -25;-10;-5;-12           
2   1               2014-03-01     -25;-10;-18          
3   1               2014-03-02     -25;-15;-14  

I need to make a select that returns the sum of the field avaliacao_postura doing the sum on all equal and negative dates and the result grouped by date. The result would have to be like in this example:

funcionario_id  data           avaliacao_postura
1               2014-03-02     -106
1               2014-03-01     -53

Answer 1

I would change the design of this table. The way data is stored now you can’t bring any basic information regarding the reviews.

An example of a new structure:

                    date           smallint NOT NULL
id  funcionario_id  data           avaliacao_postura            
1   1               2014-03-02     -25
2   1               2014-03-02     -10   
3   1               2014-03-02     -5   
4   1               2014-03-02     -12             
5   1               2014-03-01     -25  
6   1               2014-03-01     -10  
7   1               2014-03-01     -18          
8   1               2014-03-02     -25
9   1               2014-03-02     -15
10  1               2014-03-02     -14

This way you get the expected result with a simple query.

SELECT funcionario_id, data, SUM(avaliacao_postura) as avaliacao_postura
FROM equipe
GROUP BY funcionario_id, data

Answer 2

Create a View in the same database of this table with this Script ( just run this snippet ):

Create View iterator (x) as
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6;

Right after run this SQL:

SELECT funcionario_id, data, sum(avaliacao_postura) avaliacao_postura  from
(
select 
    substring_index(
        substring_index(equipe.avaliacao_postura,';',iter.x), ';', -1) 
        AS avaliacao_postura, equipe.data, equipe.funcionario_id
from equipe, iterator iter
     where iter.x <= length(equipe.avaliacao_postura)-length(
        replace(equipe.avaliacao_postura,';',''))+1 order by equipe.data, iter.x) AS C
GROUP BY data
ORDER BY data

It’s a reasonable way and it worked...

If I were you I would change the layout of your table to compose to each row an item of the evalua_posture, but, this routine will break item by item within the string separated by ; and relates to iterator view items.