Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id A7DA6475F6A for ; Sun, 19 May 2002 11:00:46 -0400 (EDT) Received: from temp.joelburton.com (unknown [209.190.238.101]) by postgresql.org (Postfix) with ESMTP id 3FAB34760E4 for ; Sun, 19 May 2002 10:58:18 -0400 (EDT) Received: from joeltf0d13cyyp (unknown [209.190.238.100]) by temp.joelburton.com (Postfix) with SMTP id 6F2A22BD0D; Sun, 19 May 2002 12:41:01 -0400 (EDT) From: "Joel Burton" To: "Dan MacNeil" , "pgsql-sql" Subject: Re: count(boolean) Date: Sun, 19 May 2002 10:58:16 -0400 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Importance: Normal In-Reply-To: <001d01c1ff3f$d7d86470$b11a9318@prometheus> X-Archive-Number: 200205/195 X-Sequence-Number: 7830 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 >