Re: Counting booleans for two columns

2009/11/20 Rikard Bosnjakovic <rikard(dot)bosnjakovic(at)gmail(dot)com>

> I have a table with values similiar to this (I have reduced the number
> of rows from the actual table):
>
> played | stats_exists
> --------+--------------
> t | t
> t | f
> t | t
> t | t
> f | t
> t | t
> t | f
>
> What I want to do is to count the number of "t" in each column and
> return the values (in two separate columns). The only thing I've
> managed to do is doing a UNION, but this gives me the (correct)
> results in one column only, I want the results in two (I need to
> distinguish between the numbers). I did like this:
>
> SELECT count(played) AS played
> FROM matches
> WHERE origin=1 AND played AND NOT training AND match_date > '2009-08-01'
> UNION
> SELECT count(stats_exists) AS stats
> FROM matches
> WHERE origin=1 AND stats_exists AND NOT training AND match_date >
> '2009-08-01';
>
> with this result:
>
> played
> --------
> 12 <-- stats
> 13 <-- played
> (2 rows)
>
> How can I rewrite the SELECT so I get two values in separate columns
> instead of two rows?
>
>
> Try:

SELECT sum(played::integer), sum(stats_exists::integer)
FROM matches
WHERE origin=1 AND stats_exists AND NOT training AND match_date >
'2009-08-01';

If you're using an old version of PostgreSQL, you might have to use:

SELECT sum(case played when true then 1 else 0 end), sum(case stats_exists
when true then 1 else 0 end)
FROM matches
WHERE origin=1 AND stats_exists AND NOT training AND match_date >
'2009-08-01';

Regards

Thom