How to make a Select with multiple SUM?

Question

Hello, I have a table of activities that lists activity data, including her status.

I would like to sum up the activities of a particular user and display in a graph, showing according to the status of the activity. (Type, always add +1 according to status).

For example:

The user Bruno has: 25 activities with expired status. 10 activities with pending status. 30 activities with completed status.

I’m trying to use the code below in Mysql:

SELECT  
  SUM(CASE WHEN STATUS = 'CONCLUIDO' THEN 1 ELSE 0) AS Concluido,
  SUM(CASE WHEN STATUS = 'CONCLUIDO_VENCIDO' THEN 1 ELSE 0) AS Concluido_Vencido,
  SUM(CASE WHEN STATUS = 'INICIADO' THEN 1 ELSE 0) AS Iniciado,
  SUM(CASE WHEN STATUS = 'INICIADO_VENCIDO' THEN 1 ELSE 0) AS Iniciado_Vencido,
  SUM(CASE WHEN STATUS = 'PENDENTE' THEN 1 ELSE 0) AS Pendente,
  SUM(CASE WHEN STATUS = 'VENCIDO' THEN 1 ELSE 0) AS Vencido
FROM tbl_atividades WHERE responsavel = "BRUNO";

• The code is giving error.

Description of table tbl_activities:

** Follows the error presented **

Thank you!

Answer 1

The query is almost right, but lacked a detail to work properly, try this way it will certainly work.

SELECT  
  SUM(CASE WHEN STATUS = 'CONCLUIDO' THEN 1 ELSE 0 END) AS Concluido,
  SUM(CASE WHEN STATUS = 'CONCLUIDO_VENCIDO' THEN 1 ELSE 0 END) AS Concluido_Vencido,
  SUM(CASE WHEN STATUS = 'INICIADO' THEN 1 ELSE 0 END) AS Iniciado,
  SUM(CASE WHEN STATUS = 'INICIADO_VENCIDO' THEN 1 ELSE 0 END) AS Iniciado_Vencido,
  SUM(CASE WHEN STATUS = 'PENDENTE' THEN 1 ELSE 0 END) AS Pendente,
  SUM(CASE WHEN STATUS = 'VENCIDO' THEN 1 ELSE 0 END) AS Vencido
FROM tbl_atividades WHERE responsavel = "BRUNO";

Answer 2

What you are trying to do is count the number of activities per user. I imagine the following script solves your problem:

select status, count(*)
from tbl_atividades
where responsavel = 'BRUNO'
group by status;

This will show how many occurrences of each status exists for that particular user.