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';
