From: Jean-Luc Lachance <jllachan@sympatico.ca>
To: Martin Kuria <martinkuria@hotmail.com>
Cc: dev@archonet.com
Cc: olly@lfix.co.uk
Cc: middink@indo.net.id
Cc: pgsql-sql@postgresql.org
Subject: Re: SUM() & GROUP BY
Date: Fri, 07 May 2004 13:31:36 -0400
Message-ID: <409BC7F8.5070309@sympatico.ca> (raw)
In-Reply-To: <Sea2-F17FNpyLAPDVXL0000f045@hotmail.com>
References: <Sea2-F17FNpyLAPDVXL0000f045@hotmail.com>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jllachan@sympatico.ca, martinkuria@hotmail.com, dev@archonet.com, olly@lfix.co.uk, middink@indo.net.id
Subject: Re: SUM() & GROUP BY
In-Reply-To: <409BC7F8.5070309@sympatico.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox