pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcount(boolean)
4+ messages / 3 participants
[nested] [flat]
* count(boolean)
@ 2002-05-19 14:16 Dan MacNeil <dan@ltc.org>
0 siblings, 2 replies; 4+ messages in thread
From: Dan MacNeil @ 2002-05-19 14:16 UTC (permalink / raw)
To: pgsql-sql
I have a table answers_boolean:
question_id | integer | not null
evaluation_id | integer | not null
value | boolean |
I'd like output in the form:
question_id, count_true, count_false
....where count_true is the number of questions answered "true"
SELECT
question_id AS id, value AS val , count(value) AS cnt
FROM
answers_boolean
GROUP BY
question_id,value;
gives me:
id | val | cnt
----+-----+-----
2 | f | 3
2 | t | 3
3 | f | 2
3 | t | 4
...which is sorta what I want.
I've looked through the docs & archive but haven't found the answer.
TIA
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: count(boolean)
@ 2002-05-19 14:58 Joel Burton <joel@joelburton.com>
parent: Dan MacNeil <dan@ltc.org>
1 sibling, 0 replies; 4+ messages in thread
From: Joel Burton @ 2002-05-19 14:58 UTC (permalink / raw)
To: Dan MacNeil <dan@ltc.org>; pgsql-sql
SELECT question_id,
COUNT(*)
FROM Ansers_Boolean
WHERE value=TRUE
GROUP BY question_id;
sort of works; it's fast & correct, but if a question has no true responses,
it leaves off that question. This may or may not be acceptable to you.
You could fix that with:
SELECT question_id,
COUNT(*)
FROM Answers_Boolean
WHERE value=TRUE
GROUP BY question_id
UNION ALL
SELECT question_id,
0
FROM Answers_Boolean AS AB0
WHERE NOT EXISTS (SELECT *
FROM Answers_Boolean AS AB1
WHERE AB0.question_id = AB1.question_id
AND value = TRUE);
But it performs slowly. I normally create turn the bools into 1 or 0 and add
them up (called a "Characteristic function"):
SELECT question_id,
SUM(CASE value WHEN TRUE THEN 1 ELSE 0 END) AS num_true
FROM Answers_Boolean
GROUP BY question_id;
Plus, it's easy this way to add a num_false column, by just copying
num_true.
Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Dan MacNeil
> Sent: Sunday, May 19, 2002 10:17 AM
> To: pgsql-sql
> Subject: [SQL] count(boolean)
>
>
> I have a table answers_boolean:
>
> question_id | integer | not null
> evaluation_id | integer | not null
> value | boolean |
>
> I'd like output in the form:
>
> question_id, count_true, count_false
>
> ....where count_true is the number of questions answered "true"
>
> SELECT
> question_id AS id, value AS val , count(value) AS cnt
> FROM
> answers_boolean
> GROUP BY
> question_id,value;
>
> gives me:
> id | val | cnt
> ----+-----+-----
> 2 | f | 3
> 2 | t | 3
> 3 | f | 2
> 3 | t | 4
>
> ...which is sorta what I want.
>
> I've looked through the docs & archive but haven't found the answer.
>
> TIA
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: 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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: count(boolean)
@ 2002-05-20 15:08 Jean-Luc Lachance <jllachan@nsd.ca>
parent: Dan MacNeil <dan@ltc.org>
1 sibling, 1 reply; 4+ messages in thread
From: Jean-Luc Lachance @ 2002-05-20 15:08 UTC (permalink / raw)
To: Dan MacNeil <dan@ltc.org>; +Cc: pgsql-sql
Dan,
Try:
SELECT
question_id AS id,
count( case when value then 1 else 0 end) as count_true,
count( case when value then 0 else 1 end) as count_false
FROM
answers_boolean
GROUP BY
question_id;
jll
Dan MacNeil wrote:
>
> I have a table answers_boolean:
>
> question_id | integer | not null
> evaluation_id | integer | not null
> value | boolean |
>
> I'd like output in the form:
>
> question_id, count_true, count_false
>
> ....where count_true is the number of questions answered "true"
>
> SELECT
> question_id AS id, value AS val , count(value) AS cnt
> FROM
> answers_boolean
> GROUP BY
> question_id,value;
>
> gives me:
> id | val | cnt
> ----+-----+-----
> 2 | f | 3
> 2 | t | 3
> 3 | f | 2
> 3 | t | 4
>
> ...which is sorta what I want.
>
> I've looked through the docs & archive but haven't found the answer.
>
> TIA
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: count(boolean)
@ 2002-05-20 16:07 Jean-Luc Lachance <jllachan@nsd.ca>
parent: Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 0 replies; 4+ messages in thread
From: Jean-Luc Lachance @ 2002-05-20 16:07 UTC (permalink / raw)
To: Dan MacNeil <dan@ltc.org>; pgsql-sql
Actually, "count" should be "sum"
SELECT
question_id AS id,
sum( case when value then 1 else 0 end) as count_true,
sum( case when value then 0 else 1 end) as count_false
FROM
answers_boolean
GROUP BY
question_id;
I wrote:
>
> Dan,
>
> Try:
>
> SELECT
> question_id AS id,
> count( case when value then 1 else 0 end) as count_true,
> count( case when value then 0 else 1 end) as count_false
> FROM
> answers_boolean
> GROUP BY
> question_id;
>
> jll
>
> Dan MacNeil wrote:
> >
> > I have a table answers_boolean:
> >
> > question_id | integer | not null
> > evaluation_id | integer | not null
> > value | boolean |
> >
> > I'd like output in the form:
> >
> > question_id, count_true, count_false
> >
> > ....where count_true is the number of questions answered "true"
> >
> > SELECT
> > question_id AS id, value AS val , count(value) AS cnt
> > FROM
> > answers_boolean
> > GROUP BY
> > question_id,value;
> >
> > gives me:
> > id | val | cnt
> > ----+-----+-----
> > 2 | f | 3
> > 2 | t | 3
> > 3 | f | 2
> > 3 | t | 4
> >
> > ...which is sorta what I want.
> >
> > I've looked through the docs & archive but haven't found the answer.
> >
> > TIA
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-05-20 16:07 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-05-19 14:16 count(boolean) Dan MacNeil <dan@ltc.org>
2002-05-19 14:58 ` Joel Burton <joel@joelburton.com>
2002-05-20 15:08 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-05-20 16:07 ` Jean-Luc Lachance <jllachan@nsd.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox