Re: SUM() & GROUP BY

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.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
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>