X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 19463D1DB81 for ; Fri, 7 May 2004 08:32:50 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 29576-04 for ; Fri, 7 May 2004 08:32:31 -0300 (ADT) Received: from hotmail.com (sea2-f17.sea2.hotmail.com [207.68.165.17]) by svr1.postgresql.org (Postfix) with ESMTP id 51A67D1E2CA for ; Fri, 7 May 2004 08:32:28 -0300 (ADT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Fri, 7 May 2004 04:32:31 -0700 Received: from 80.205.253.69 by sea2fd.sea2.hotmail.msn.com with HTTP; Fri, 07 May 2004 11:32:31 GMT X-Originating-IP: [80.205.253.69] X-Originating-Email: [martinkuria@hotmail.com] X-Sender: martinkuria@hotmail.com From: "Martin Kuria" To: dev@archonet.com Cc: olly@lfix.co.uk, middink@indo.net.id, pgsql-sql@postgresql.org Subject: Re: SUM() & GROUP BY Date: Fri, 07 May 2004 14:32:31 +0300 Mime-Version: 1.0 Content-Type: text/plain; format=flowed Message-ID: X-OriginalArrivalTime: 07 May 2004 11:32:31.0436 (UTC) FILETIME=[FBFE38C0:01C43426] X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/69 X-Sequence-Number: 17783 Thanks Huxton, Sorry for not explaining fully here is what I would like to achieve: When I do: SELECT s.pd_geo, COUNT(s.pd_geo) FROM ser s GROUP BY s.pd_geo; OUTPUT: pd_geo | count ---------------------- 1 | 49 2 | 39 3 | 6 4 | 54 It outputs the number of entries the Divisions have made Here is what Division table contains: SELECT * FROM ser_divisions; divisions_name | divisions_id --------------------------------------- DEE | 3131 DEPI | 3133 DED | 3134 GBH | 3136 Now I would like to get to know how each Division answered i.e. SELECT s.pd_geo, COUNT(s.pd_geo) FROM ser s WHERE s.ser_divisions = '3131' GROUP BY s.pd_geo; output: pd_geo | count ---------------------- 1 | 9 2 | 2 3 | 6 4 | 5 But this is the output I intend to get: divisions_name | pd_geo | count ----------------------------------------------- DEE | 1 | 9 DEE | 2 | 2 DEE | 3 | 6 DEE | 4 | 5 How do I achieve the above results please do advice thanks again. Kind Regards +-----------------------------------------------------+ | Martin W. Kuria (Mr.) martin.kuria@unon.org +----------------------------------------------------+ >From: Richard Huxton <dev@archonet.com> >To: Martin Kuria <martinkuria@hotmail.com> >CC: olly@lfix.co.uk, middink@indo.net.id, pgsql-sql@postgresql.org >Subject: Re: [SQL] SUM() & GROUP BY >Date: Fri, 07 May 2004 09:00:43 +0100 > >Martin Kuria wrote: >>Hi again I have two tables I would like to query i.e. service table >>and division table >> >>SELECT s.pd_geo, COUNT(s.pd_geo) FROM ser s GROUP BY s.pd_geo; >>OUTPUT: >>pd_geo | count >>---------------------- >> 1 | 49 >> 2 | 39 >> 3 | 6 >> 4 | 54 >> >>SELECT d.divisions_name, d.divisions_id) >>FROM ser s, ser_divisions d >>WHERE d.divisions_id = s.ser_divisions; >> >>division_name | divisions_id >>-------------------------------------- >> DEC | 6 >> DEPI | 7 >> DRC | 8 >> >>How can I create a query that displays How the divisions answered >>the question please do assist. > >Martin - you'll need to explain exactly what you want. Can you show >what outputs you would like given the above data? > >-- > Richard Huxton > Archonet Ltd _________________________________________________________________ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail