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 80DA0D1BAF5 for ; Fri, 7 May 2004 03:47:57 -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 21104-09 for ; Fri, 7 May 2004 03:47:37 -0300 (ADT) Received: from hotmail.com (sea2-f26.sea2.hotmail.com [207.68.165.26]) by svr1.postgresql.org (Postfix) with ESMTP id 544B6D1ECC8 for ; Fri, 7 May 2004 03:47:35 -0300 (ADT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Thu, 6 May 2004 23:47:36 -0700 Received: from 80.205.253.69 by sea2fd.sea2.hotmail.msn.com with HTTP; Fri, 07 May 2004 06:47:35 GMT X-Originating-IP: [80.205.253.69] X-Originating-Email: [martinkuria@hotmail.com] X-Sender: martinkuria@hotmail.com From: "Martin Kuria" To: olly@lfix.co.uk, middink@indo.net.id Cc: pgsql-sql@postgresql.org Subject: Re: SUM() & GROUP BY Date: Fri, 07 May 2004 09:47:35 +0300 Mime-Version: 1.0 Content-Type: text/plain; format=flowed Message-ID: X-OriginalArrivalTime: 07 May 2004 06:47:36.0085 (UTC) FILETIME=[2E5EAC50:01C433FF] 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/62 X-Sequence-Number: 17776 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. Regrards Martin W. Kuria >From: Oliver Elphick <olly@lfix.co.uk> >To: "Muhyiddin A.M Hayat" <middink@indo.net.id> >CC: pgsql-sql@postgresql.org >Subject: Re: [SQL] SUM() & GROUP BY >Date: Sun, 28 Sep 2003 20:56:56 +0100 > >On Sun, 2003-09-28 at 19:01, Muhyiddin A.M Hayat wrote: > > > > hotel=# SELECT > > hotel-# "public".billing.id, > > hotel-# "public".billing.guest_id, > > hotel-# "public".billing.trx_date, > > hotel-# "public".billing.trx_time, > > hotel-# "public".billing.payment_method, > > hotel-# "public".billing.tax, > > hotel-# "public".billing.dep_id, > > hotel-# "public".department."name", > > hotel-# SUM("public".items.price) AS total, > > hotel-# "public".billing.amount_paid > > hotel-# FROM > > hotel-# "public".billing_items > > hotel-# INNER JOIN "public".billing ON > > ("public".billing_items.billing_id = "public".billing.id) > > hotel-# INNER JOIN "public".department ON ("public".billing.dep_id = > > "public".department.id) > > hotel-# INNER JOIN "public".items ON > > ("public".billing_items.items_id = "public".items.id) > > hotel-# GROUP BY "public".billing.id; > > ERROR: Attribute billing.guest_id must be GROUPed or used in an > > aggregate function > > hotel=# > > > > What Worng ?? > >Any items in the select list need to be aggregated (e.g. >SUM("public".items.price)) or mentioned in the GROUP BY list. Suppose >there are several billing.guest_id values for each billing.id; which >value should be listed in the output? > > >-- >Oliver Elphick Oliver.Elphick@lfix.co.uk >Isle of Wight, UK http://www.lfix.co.uk/oliver >GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C > ======================================== > "Blessed is the man that endureth temptation; for when > he is tried, he shall receive the crown of life, which > the Lord hath promised to them that love him." > James 1:12 > > >---------------------------(end of broadcast)--------------------------- >TIP 8: explain analyze is your friend _________________________________________________________________ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail