Re: SUM() & GROUP BY

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