Someone just posted a problem on a PostgreSQL mailing list. They had several boolean columns, but they wanted to get how many values were true for each column, but in 1 query. There are several ways to do this, but none of which are particularly elegant.
Say we have the following table definition:
CREATE TABLE subscribers
(
subscriber text PRIMARY KEY,
news boolean DEFAULT false,
sport boolean DEFAULT false,
promotions boolean DEFAULT false,
horrible_spam boolean DEFAULT false
);
INSERT INTO subscribers (subscriber, news, sport, promotions, horrible_spam)
VALUES
('person_a', true, false, false, false),
('person_b', true, true, true, false),
('person_c', true, false, true, true),
('person_d', false, false, false, false),
('person_e', true, true, false, false),
('person_f', true, false, true, true),
('person_g', true, false, false, false);
Now we want to get a count of how many of each subscription category has someone subscribing to it. We can use a case statement for each column:
SELECT sum(CASE news WHEN true THEN 1 END) AS "news count",
sum(CASE sport WHEN true THEN 1 END) as "sport count",
sum(CASE promotions WHEN true THEN 1 END) as "promotions count",
sum(CASE horrible_spam WHEN true THEN 1 END) as "horrible_spam count"
FROM subscribers;
Which produces:
news count | sport count | promotions count | horrible_spam count
------------+-------------+------------------+---------------------
6 | 2 | 3 | 2
(1 row)
This looks almost unreadable and is very cumbersome. There's another way however, although it's no better. We use 4 subqueries:
SELECT (SELECT count(news) FROM subscribers WHERE news = true) AS "news count",
(SELECT count(sport) FROM subscribers WHERE sport = true) AS "sport count",
(SELECT count(promotions) FROM subscribers WHERE promotions = true) AS "promotions count",
(SELECT count(horrible_spam) FROM subscribers WHERE horrible_spam = true) AS "horrible_spam count";
news count | sport count | promotions count | horrible_spam count
------------+-------------+------------------+---------------------
6 | 2 | 3 | 2
(1 row)
Arghhh.. my eyes! Okay, we can try casting booleans to integers:
SELECT count(news::int) AS "news count",
count(sport::int) AS "sport count",
count(promotions::int) AS "promotions count",
count(horrible_spam::int) AS "horrible_spam count"
FROM subscribers;
news count | sport count | promotions count | horrible_spam count
------------+-------------+------------------+---------------------
7 | 7 | 7 | 7
(1 row)
Hmmm.. obviously not.
Surely there's a better way? Well no, there isn't... unless we create an aggregate function which will count based on an expression.
CREATE OR REPLACE FUNCTION countif_add(current_count int, expression bool)
RETURNS int AS
$BODY$
SELECT CASE expression
WHEN true THEN
current_count + 1
ELSE
current_count
END;
$BODY$
LANGUAGE SQL IMMUTABLE;
CREATE AGGREGATE count_if (boolean)
(
sfunc = countif_add,
stype = int,
initcond = 0
);
Now we can use a far tidier query.
SELECT count_if(news) AS "news count",
count_if(sport) AS "sport count",
count_if(promotions) AS "promotions count",
count_if(horrible_spam) AS "horrible_spam count"
FROM subscribers;
news count | sport count | promotions count | horrible_spam count
------------+-------------+------------------+---------------------
6 | 2 | 3 | 2
(1 row)
But this function lets us do more than count true values in boolean columns. It's evaluating expressions. So you can have queries like:
SELECT count_if(my_column > 7) AS "things bigger than 7"
count_if(this_column = that_column) AS "stuff that matches"
FROM my_table;
