Count the boolean values in a table and group by a column using sql-CodePudding

Data in DB:

ID  alarmTime   isActive
1   1/3/2021    FALSE
2   1/3/2021    FALSE
3   1/3/2021    FALSE
4   1/5/2021    TRUE
5   1/5/2021    TRUE

Required Format:

alarmTime   Active_count    Inactive_count
1/3/2021    0               3
1/5/2021    2               0

I am trying this but it is not working:

SELECT
    alarmTime,
    COUNT(CASE WHEN isActive = TRUE then 1 ELSE NULL END) as "Active_count",
    COUNT(CASE WHEN isActive = FALSE then 1 ELSE NULL END) as "Inactive_count"
from historicAlarms
GROUP BY alarmTime

Answer: I forgot to add quotes around True and False. The query should be:

SELECT
    AlarmTime,
    COUNT(CASE WHEN isActive = 'TRUE' then 1 ELSE NULL END) as "Active_count",
    COUNT(CASE WHEN isActive = 'FALSE' then 1 ELSE NULL END) as "Inactive_count"
from historicAlarms
GROUP BY AlarmTime

CodePudding user response：

Try this:

select alarmtime,
sum(case isactive when true then 1 else 0 end) as active_count,
sum(case isactive when false then 1 else 0 end) as inactive_count
from historicAlarms
group by alarmtime;

CodePudding user response：

The answer depends on the data type the column "isActive" has. Maybe it will be enough to just replace COUNT BY SUM.

SELECT
alarmTime,
SUM(CASE WHEN isActive = TRUE THEN 1 ELSE 0 END) AS "Active_count",
SUM(CASE WHEN isActive = FALSE THEN 1 ELSE 0 END) AS "Inactive_count"
FROM historicAlarms1
GROUP BY alarmTime;

If it's a varchar column, TRUE or FALSE must be quoted.

SELECT
alarmTime,
SUM(CASE WHEN isActive = 'TRUE' THEN 1 ELSE 0 END) AS "Active_count",
SUM(CASE WHEN isActive = 'FALSE' THEN 1 ELSE 0 END) AS "Inactive_count"
FROM historicAlarms
GROUP BY alarmTime