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 ACCB8D1F49F for ; Fri, 7 May 2004 14:31:13 -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 57508-09 for ; Fri, 7 May 2004 14:30:53 -0300 (ADT) Received: from simmts12-srv.bellnexxia.net (simmts12.bellnexxia.net [206.47.199.141]) by svr1.postgresql.org (Postfix) with ESMTP id 3FE4ED1ED74 for ; Fri, 7 May 2004 14:30:52 -0300 (ADT) Received: from localhost ([67.71.24.121]) by simmts12-srv.bellnexxia.net (InterMail vM.5.01.06.05 201-253-122-130-105-20030824) with ESMTP id <20040507173052.KNCI15788.simmts12-srv.bellnexxia.net@localhost>; Fri, 7 May 2004 13:30:52 -0400 Received: from sympatico.ca ([192.168.250.6]) by (8.11.6/8.11.6) with ESMTP id i47HUmg02303; Fri, 7 May 2004 13:30:48 -0400 Message-ID: <409BC7F8.5070309@sympatico.ca> Date: Fri, 07 May 2004 13:31:36 -0400 From: Jean-Luc Lachance User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Martin Kuria Cc: dev@archonet.com, olly@lfix.co.uk, middink@indo.net.id, pgsql-sql@postgresql.org Subject: Re: SUM() & GROUP BY References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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/74 X-Sequence-Number: 17788 Try: SELECT d.divisions_name, s.pd_geo, COUNT(s.pd_geo) FROM ser s, ser_divisions d WHERE s.ser_divisions = '3131' AND s.ser_divisions = d.divisions_id GROUP BY d.divisions_name, s.pd_geo; Martin Kuria wrote: > 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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >