CODEWITHSUNDEEP
mysqlsqldatabase
Dev Gourav
Dev Gourav

Reputation: 136

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

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

Upvotes: 1

Views: 1733

Answers (2)

Jonas Metzler
Jonas Metzler

Reputation: 5975

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

Upvotes: 0

blabla_bingo
blabla_bingo

Reputation: 2152

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;

Upvotes: 2

Related Questions