Postgres Pro
Downloads
Home   >   mailing lists

Thread: Count

Count

From
Bob Pawley
Date:
I have a table with four columns that will either be null or hold the value
'true'.

I want to obtain the count of these columns, within a particular row, that
have 'true' as a value (0 to 4).

I have attempted the Select count method but it seems that I need something
more.

If anyone has any thoughts it would be much appreciated.

Bob

Re: Count

From
Bricklen Anderson
Date:
Bob Pawley wrote:
> I have a table with four columns that will either be null or hold the
> value 'true'.
>
> I want to obtain the count of these columns, within a particular row,
> that have 'true' as a value (0 to 4).
>
> I have attempted the Select count method but it seems that I need
> something more.
>
> If anyone has any thoughts it would be much appreciated.
>
> Bob

Something like this?

create table t (id int, w bool, x bool, y bool, z bool);
insert into t values
(1,null,null,'t','t'),
(1,null,'t','t',null),
(2,'t',null,'t',null),
(2,'t',null,'t',null),
(3,null,'t','t','t'),
(4,'t','t','t','t');

select id,
        sum(case when w is null then 0 else 1 end) as w,
        sum(case when x is null then 0 else 1 end) as x,
        sum(case when y is null then 0 else 1 end) as y,
        sum(case when z is null then 0 else 1 end) as z
from t
group by id
order by id;

  id | w | x | y | z
----+---+---+---+---
   1 | 0 | 1 | 2 | 1
   2 | 2 | 0 | 2 | 0
   3 | 0 | 1 | 1 | 1
   4 | 1 | 1 | 1 | 1

?

Re: Count

From
Jeff Davis
Date:
On Wed, 2008-01-23 at 10:50 -0800, Bob Pawley wrote:
> I have a table with four columns that will either be null or hold the value
> 'true'.
>
> I want to obtain the count of these columns, within a particular row, that
> have 'true' as a value (0 to 4).
>
> I have attempted the Select count method but it seems that I need something
> more.
>
> If anyone has any thoughts it would be much appreciated.

1. what did you do?
2. what did you see?
3. what did you expect?

Regards,
    Jeff Davis

Re: Count

From
brian
Date:
Bob Pawley wrote:
> I have a table with four columns that will either be null or hold the
> value 'true'.

Any reason why the NULL values aren't instead FALSE? NULL != FALSE.

> I want to obtain the count of these columns, within a particular row,
> that have 'true' as a value (0 to 4).

SELECT
     (CASE WHEN t.col1 = TRUE THEN 1 ELSE 0 END)
   + (CASE WHEN t.col2 = TRUE THEN 1 ELSE 0 END)
   + (CASE WHEN t.col3 = TRUE THEN 1 ELSE 0 END)
   + (CASE WHEN t.col4 = TRUE THEN 1 ELSE 0 END)
AS true_count FROM your_table AS t;

I'm sure there's a more clever way than that, though.

> I have attempted the Select count method but it seems that I need
> something more.

That would only be useful if you were counting across rows (aggregating).

b

Re: Count

From
Charles Simard
Date:
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Bob Pawley
> Sent: 23 janvier 2008 13:51
> To: PostgreSQL
> Subject: [GENERAL] Count
>
>
> I have a table with four columns that will either be null or hold the
value
> 'true'.
>
> I want to obtain the count of these columns, within a particular row, that
> have 'true' as a value (0 to 4).
>
> I have attempted the Select count method but it seems that I need
something
> more.
>
> If anyone has any thoughts it would be much appreciated.
>
> Bob
>

Or something like this  ?

create table test (
    id_test serial,
    c1 boolean,
    c2 boolean,
    c3 boolean,
    c4 boolean
);

insert into test (c1,c2,c3,c4) values ( true, null, null, true),( true,
true, null, true),( null, null, null, null);

select id_test, (case when c1 is null then 0 else 1 end)+(case when c2 is
null then 0 else 1 end)+(case when c3 is null then 0 else 1 end)+(case when
c4 is null then 0 else 1 end) as total from test;

 id_test | total
---------+-------
       1 |     2
       2 |     3
       3 |     0

Regards,

Charles Simard