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


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailtogsql-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


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

?

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq