CODEWITHSUNDEEP
sqlfirebird
Felipe Januário
Felipe Januário

Reputation: 41

How to make multiple COUNT with different conditions in single select statement

I made three selects below where each one returns a value. How do I do a select only and return the three values, each with its condition? All information is in one table.

SELECT COUNT (GRA_HRFIM) as "Conferidos antes 17h" FROM GRADE WHERE GRA_HRFIM < '17:00:00' AND GRA_DATADIGITA = '04/28/2021'

SELECT COUNT (GRA_HORADIGITA) as "Importados antes 17h" FROM GRADE WHERE GRA_HORADIGITA < '17:00:00' AND GRA_DATADIGITA = '04/28/2021'

SELECT COUNT (GRA_HRFIM) as "Total pedidos" FROM GRADE WHERE GRA_DATADIGITA = '04/28/2021'

Upvotes: 4

Views: 2231

Answers (1)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

Condition GRA_DATADIGITA = '04/28/2021' is same for all the counts so use it in where clause. Now you have third count by simply selecting COUNT (GRA_HRFIM) but for the first and second count you need to use case when statement to select conditional count.

SELECT COUNT(case when GRA_HRFIM < '17:00:00' then GRA_HRFIM else null end)
         AS "Conferidos antes 17h",
       COUNT(case when GRA_HORADIGITA < '17:00:00' then GRA_HORADIGITA else null end)
         AS  "Importados antes 17h",
       COUNT(GRA_HRFIM)
         AS "Total pedidos"
  FROM GRADE
 WHERE GRA_DATADIGITA = '04/28/2021';

You can also avoid mentioning else part of case when statements since default value is null.

SELECT COUNT(case when GRA_HRFIM < '17:00:00' then GRA_HRFIM end)
             AS "Conferidos antes 17h",
           COUNT(case when GRA_HORADIGITA < '17:00:00' then GRA_HORADIGITA end)
             AS  "Importados antes 17h",
           COUNT(GRA_HRFIM)
             AS "Total pedidos"
      FROM GRADE
     WHERE GRA_DATADIGITA = '04/28/2021';

Upvotes: 6

Related Questions