Doubt group by + sum function

Question

Doubt group by + sum function

Navigation

#1 by (1 votes)

0

I have a problem putting a query into my mysql database for attendee attendance at an event.

I have a participant table where I have participant information, and it has the attendance of those participants on days 1, 2 and 3 (the columns d1, d2, d3) where I store 0 for non-frequency and 1 for the frequency on the respective day .

Well, now I need to make an appointment where you can tell me how many days each subscribed subscriber was present: 0 (if there is no day), 1, 2 or 3 days, I'm doing this with the SUM(d1 + d2 + d3) function.

Now I need to apply the group by to the SUM function to group the counts of how many participants were present in 0, 1, 2 or 3 days. But I get the error 1056 .

In what other way can I do the following query?

Query

SELECT count(*) as cont, SUM(d1 + d2 + d3) as dias
FROM guiatudoonline_cadastro.participantes
GROUP BY dias

mysql group-by count

asked by anonymous 11.04.2016 / 21:45

1 answer

Leave only one column of the editable JTable DIV align dynamically as a stained glass window

score 1 · Accepted Answer

Your query does not do what you say, starting with "each participant," since you did not include the participant identifier in the SELECT.

To know how many days each participant attended, you should do:

SELECT participante, (d1 + d2 + d3) qtde_dias
FROM guiatudoonline_cadastro.participantes

To find out how many participants attended on how many days:

SELECT (d1 + d2 + d3) qtde_dias, count(*)
FROM guiatudoonline_cadastro.participantes
GROUP BY qtde_dias

Sum () does not sum the values of a row, but between several rows that share the aggregation criteria that you specified. In his queries he was not needed.

The first query did not need aggregation criteria because I understood that each participant appears only once in the query, since d1, d2 and d3 are distinct columns. If you had modeled the columns as (participant, date) then there would be one aggregation per participant and one COUNT (date).