Re: SUM() & GROUP BY

From: Jean-Luc Lachance <jllachan(at)sympatico(dot)ca>
To: Martin Kuria <martinkuria(at)hotmail(dot)com>
Cc: dev(at)archonet(dot)com, olly(at)lfix(dot)co(dot)uk, middink(at)indo(dot)net(dot)id, pgsql-sql(at)postgresql(dot)org
Subject: Re: SUM() & GROUP BY
Date: 2004-05-07 17:31:36
Message-ID: 409BC7F8.5070309@sympatico.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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(dot)kuria(at)unon(dot)org
> +----------------------------------------------------+
>
>
>
>
> &gt;From: Richard Huxton &lt;dev(at)archonet(dot)com&gt;
> &gt;To: Martin Kuria &lt;martinkuria(at)hotmail(dot)com&gt;
> &gt;CC: olly(at)lfix(dot)co(dot)uk, middink(at)indo(dot)net(dot)id, pgsql-sql(at)postgresql(dot)org
> &gt;Subject: Re: [SQL] SUM() &amp; GROUP BY
> &gt;Date: Fri, 07 May 2004 09:00:43 +0100
> &gt;
> &gt;Martin Kuria wrote:
> &gt;&gt;Hi again I have two tables I would like to query i.e. service table
> &gt;&gt;and division table
> &gt;&gt;
> &gt;&gt;SELECT s.pd_geo, COUNT(s.pd_geo) FROM ser s GROUP BY s.pd_geo;
> &gt;&gt;OUTPUT:
> &gt;&gt;pd_geo | count
> &gt;&gt;----------------------
> &gt;&gt; 1 | 49
> &gt;&gt; 2 | 39
> &gt;&gt; 3 | 6
> &gt;&gt; 4 | 54
> &gt;&gt;
> &gt;&gt;SELECT d.divisions_name, d.divisions_id)
> &gt;&gt;FROM ser s, ser_divisions d
> &gt;&gt;WHERE d.divisions_id = s.ser_divisions;
> &gt;&gt;
> &gt;&gt;division_name | divisions_id
> &gt;&gt;--------------------------------------
> &gt;&gt; DEC | 6
> &gt;&gt; DEPI | 7
> &gt;&gt; DRC | 8
> &gt;&gt;
> &gt;&gt;How can I create a query that displays How the divisions answered
> &gt;&gt;the question please do assist.
> &gt;
> &gt;Martin - you'll need to explain exactly what you want. Can you show
> &gt;what outputs you would like given the above data?
> &gt;
> &gt;--
> &gt; Richard Huxton
> &gt; 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
>

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Peter Eisentraut 2004-05-07 18:39:09 Re: SELECT - ORDER BY Croatian characters ....
Previous Message Suller András 2004-05-07 14:19:24 Re: COUNT on a DISTINCT query