How to sum values from a grouped field by date?

Question

How to sum values from a grouped field by date?

Navigation

#1 by (5 votes)
#2 by (1 votes)

5

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 me the sum of the avaliacao_postura field, making the sum on all dates equal and negative, and the result grouped by date. The result would have to be as in this example:

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

mysql sql-select

asked by anonymous 10.04.2014 / 00:43

2 answers

ASP.NET MVC5 - Asynchronous Methods on the Controller Convert string to Time

score 5 · Answer 1

I would change the design of this table. By the way that data is stored now you can not bring any basic information pertaining to the evaluations.

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

score 1 · Answer 2

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

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;

Logo After running 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 each line of an item of the valuation_position, but this routine will sort the item into the separated string by ; and relate to the items in the View iterator