Count

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

?

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

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

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