Re: SUM() & GROUP BY

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