Select after select result

Asked

Viewed 56 times

0

I am trying to select in a table to display a list of activities.

And second select on each of the first select lines to count how many are in each status, but I’m having syntax error, which I’m doing wrong ?

SELECT ATIVIDADE, DEPARTAMENTO,
(
    SELECT 
    COUNT(STATUS WHERE STATUS = 'PENDENTE' OR STATUS = 'INICIADO' OR  STATUS = 'INICIADO/VENCIDO') AS AFAZER,
    COUNT(STATUS WHERE STATUS = 'CONCLUIDO') AS FEITO,
    COUNT(*) AS TOTAL
    WHERE DT_VENCIMENTO BETWEEN '2019-05-01' AND '2019-05-31'
    FROM LISTA_DE_ATIVIDADES
)
FROM TIPOS_DE_ATIVIDADE

This way below I managed to return 1 single record, but there are several and with the wrong values.

SELECT DISTINCT TIPO_ATIVIDADE AS ATIVIDADE, DEPARTAMENTO,
(SELECT COUNT(CASE WHEN (STATUS = 'PENDENTE' OR STATUS = 'INICIADO' OR STATUS = 'INICIADO/VENCIDO') THEN 1 ELSE 0 END))  AS AFAZER,
(SELECT COUNT(CASE WHEN STATUS = 'CONCLUIDO' THEN 1 ELSE 0 END)) AS FEITO,
(SELECT COUNT(*)) AS TOTAL
FROM tbl_atividades;

Upshot

inserir a descrição da imagem aqui

mysql sql mysqli

  • I believe a comma was missing before the innermost SELECT. Doesn’t it seem strange to you that you want a field, in the STATUS case, that has at the same time the content "PENDING", "STARTED" and "STARTED/EXPIRED"? In fact you should not use ' in place of "?

  • Fixed the query, but still not working, I edited with the new update

  • You are using the wrong syntax in the COUNT function. Try: COUNT(CASE WHEN (STATUS = 'PENDING' OR STATUS = 'STARTED' OR STATUS = 'STARTED/EXPIRED') THEN 1 ELSE 0 END) AS AFAZER, COUNT(CASE WHEN STATUS = 'COMPLETED' THEN 1 ELSE 0 END) AS DONE,

  • Note also that the WHERE clause comes after the FROM clause.

  • I edited the post tried a new way using the method Voce said, I do not know if I applied correctly, I managed to return a single record

  • Add the error you are receiving

  • You would not have to relate at least the most external SELECT ACTIVITY (table TIPOS_DE_ATIVIDADE) with the most internal SELECT (table LISTA_DE_ATIVIDADES)?

  • This second way that was passed to me , the query brought a single record , and with the incorrect sum.

  • I’ll try to do it here

Show 4 more comments

1 answer

1


Good afternoon,

For your query to work only use the GROUP BY.

I took the liberty of editing the query to gain performance. Use this way, it should work

SELECT DISTINCT 
  TIPO_ATIVIDADE AS ATIVIDADE, 
  DEPARTAMENTO,
  SUM(IF(STATUS IN ('PENDENTE', 'INICIADO', 'INICIADO/VENCIDO'), 1, 0)) AS AFAZER,
  SUM(IF(STATUS = 'CONCLUIDO', 1, 0)) AS FEITO,
  COUNT(*) AS TOTAL
FROM 
  tbl_atividades
GROUP BY 
  TIPO_ATIVIDADE,
  DEPARTAMENTO
;
  • 1

    Thank you very much solved the problem

Browser other questions tagged mysql sql mysqli

You are not signed in. Login or sign up in order to post.