From: | "Oliveiros C," <oliveiros(dot)cristina(at)marktest(dot)pt> |
---|---|
To: | "Thom Brown" <thombrown(at)gmail(dot)com>, "Rikard Bosnjakovic" <rikard(dot)bosnjakovic(at)gmail(dot)com> |
Cc: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Counting booleans for two columns |
Date: | 2009-11-20 15:06:33 |
Message-ID: | 2A2D38B5C3894323B34A530F557CF028@marktestcr.marktest.pt |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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';
Your approach is elegant, Thom.
But if it is to do it that way then I guess
you should drop the "AND stats_exists" part of the query because
it will filter out every line with stats_exists == f, and those (occasionally) played== t won't get summed up by the SUM() function,
ain't I right ?
Best,
Oliveiros
----- Original Message -----
From: Thom Brown
To: Rikard Bosnjakovic
Cc: pgsql-novice(at)postgresql(dot)org
Sent: Friday, November 20, 2009 2:30 PM
Subject: Re: [NOVICE] 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
From | Date | Subject | |
---|---|---|---|
Next Message | Thom Brown | 2009-11-20 15:11:42 | Re: Counting booleans for two columns |
Previous Message | Oliveiros C, | 2009-11-20 14:32:14 | Re: Counting booleans for two columns |