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.) [EMAIL PROTECTED] +----------------------------------------------------+
>From: Richard Huxton <[EMAIL PROTECTED]> >To: Martin Kuria <[EMAIL PROTECTED]> >CC: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] >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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
