Doubt group by + SUM function

Question

I have a problem putting together a query in my database mysql for attendance at an event.

I have a participating table where I have the participant’s information, and I have the attendees' presence on days 1, 2 and 3 (columns D1, D2, D3) where I store 0 for nonfrequency and 1 for the frequency on the respective day.

Well, now I need to make a query where I know how many days each participant enrolled was present: 0 (if I do not attend any day), 1, 2 or 3 days, I am doing this with the function SUM(d1 + d2 + d3).

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

How else can I do the following query ?

Query

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

Answer 1

Your query does not do what you say, starting with "each participant", as you have not included the attendee 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 know how many participants attended 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 line, but between several lines that share the aggregation criterion you specified. In his queries he was not necessary.

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 separate columns. If you had modeled the columns as (participant, date), then there would be an aggregation per participant and a COUNT(date).