Skip site navigation (1) Skip section navigation (2)

Re: SUM() & GROUP BY

From: "Martin Kuria" <martinkuria(at)hotmail(dot)com>
To: olly(at)lfix(dot)co(dot)uk, middink(at)indo(dot)net(dot)id
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: SUM() & GROUP BY
Date: 2004-05-07 06:47:35
Message-ID: Sea2-F26v29H72folri0000e4e9@hotmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
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





&gt;From: Oliver Elphick &lt;olly(at)lfix(dot)co(dot)uk&gt;
&gt;To: &quot;Muhyiddin A.M Hayat&quot; &lt;middink(at)indo(dot)net(dot)id&gt;
&gt;CC: pgsql-sql(at)postgresql(dot)org
&gt;Subject: Re: [SQL] SUM() &amp; GROUP BY
&gt;Date: Sun, 28 Sep 2003 20:56:56 +0100
&gt;
&gt;On Sun, 2003-09-28 at 19:01, Muhyiddin A.M Hayat wrote:
&gt; &gt;
&gt; &gt; hotel=# SELECT
&gt; &gt; hotel-#   &quot;public&quot;.billing.id,
&gt; &gt; hotel-#   &quot;public&quot;.billing.guest_id,
&gt; &gt; hotel-#   &quot;public&quot;.billing.trx_date,
&gt; &gt; hotel-#   &quot;public&quot;.billing.trx_time,
&gt; &gt; hotel-#   &quot;public&quot;.billing.payment_method,
&gt; &gt; hotel-#   &quot;public&quot;.billing.tax,
&gt; &gt; hotel-#   &quot;public&quot;.billing.dep_id,
&gt; &gt; hotel-#   &quot;public&quot;.department.&quot;name&quot;,
&gt; &gt; hotel-#   SUM(&quot;public&quot;.items.price) AS total,
&gt; &gt; hotel-#   &quot;public&quot;.billing.amount_paid
&gt; &gt; hotel-# FROM
&gt; &gt; hotel-#   &quot;public&quot;.billing_items
&gt; &gt; hotel-#   INNER JOIN &quot;public&quot;.billing ON
&gt; &gt; (&quot;public&quot;.billing_items.billing_id = 
&quot;public&quot;.billing.id)
&gt; &gt; hotel-#   INNER JOIN &quot;public&quot;.department ON 
(&quot;public&quot;.billing.dep_id =
&gt; &gt; &quot;public&quot;.department.id)
&gt; &gt; hotel-#   INNER JOIN &quot;public&quot;.items ON
&gt; &gt; (&quot;public&quot;.billing_items.items_id = 
&quot;public&quot;.items.id)
&gt; &gt; hotel-# GROUP BY  &quot;public&quot;.billing.id;
&gt; &gt; ERROR:  Attribute billing.guest_id must be GROUPed or used in an
&gt; &gt; aggregate function
&gt; &gt; hotel=#
&gt; &gt;
&gt; &gt; What Worng ??
&gt;
&gt;Any items in the select list need to be aggregated (e.g.
&gt;SUM(&quot;public&quot;.items.price)) or mentioned in the GROUP BY list.  
Suppose
&gt;there are several billing.guest_id values for each billing.id; which
&gt;value should be listed in the output?
&gt;
&gt;
&gt;--
&gt;Oliver Elphick                                Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
&gt;Isle of Wight, UK                             
http://www.lfix.co.uk/oliver
&gt;GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
&gt;                  ========================================
&gt;      &quot;Blessed is the man that endureth temptation; for when
&gt;       he is tried, he shall receive the crown of life, which
&gt;       the Lord hath promised to them that love him.&quot;
&gt;                                           James 1:12
&gt;
&gt;
&gt;---------------------------(end of broadcast)---------------------------
&gt;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


Responses

pgsql-sql by date

Next:From: Richard HuxtonDate: 2004-05-07 08:00:43
Subject: Re: SUM() & GROUP BY
Previous:From: Tom LaneDate: 2004-05-07 01:26:33
Subject: Re: Subselect returning 2 columns

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group