Обсуждение: Counting booleans for two columns

Поиск
Список
Период
Сортировка

Counting booleans for two columns

От
Rikard Bosnjakovic
Дата:
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?


--
- Rikard

Re: Counting booleans for two columns

От
Thom Brown
Дата:
2009/11/20 Rikard Bosnjakovic <rikard.bosnjakovic@gmail.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

Re: Counting booleans for two columns

От
"Oliveiros C,"
Дата:
Howdy, Rikard,

Try Something like this

SELECT (/* Your first query */) , (/* Your second query*/) ;


Best,
Oliveiros

----- Original Message -----
From: "Rikard Bosnjakovic" <rikard.bosnjakovic@gmail.com>
To: <pgsql-novice@postgresql.org>
Sent: Friday, November 20, 2009 2:17 PM
Subject: [NOVICE] Counting booleans for two columns


>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?
>
>
> --
> - Rikard
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice

Re: Counting booleans for two columns

От
"Oliveiros C,"
Дата:
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 -----
Sent: Friday, November 20, 2009 2:30 PM
Subject: Re: [NOVICE] Counting booleans for two columns

2009/11/20 Rikard Bosnjakovic <rikard.bosnjakovic@gmail.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

Re: Counting booleans for two columns

От
Thom Brown
Дата:
2009/11/20 Oliveiros C, <oliveiros.cristina@marktest.pt>
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
 
Yes, you're right. :) To be honest I didn't really pay much attention to the content of the WHERE clause.  I just tacked it on the end from one of the original selects, but I agree that it should be removed.  So we'd end up with:

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

Thom

Re: Counting booleans for two columns

От
Rikard Bosnjakovic
Дата:
On Fri, Nov 20, 2009 at 16:11, Thom Brown <thombrown@gmail.com> wrote:

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

Thank you Thom and Oliveiros, this works perfect.

(I had to use Thom's case-code since the web hotel is still using
Postgres 7.4.6, so the boolean->integer cast did not work)


--
- Rikard