Message-ID: <409B8E5F.6090408@archonet.com>
Date: Fri, 07 May 2004 14:25:51 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 0.6 (X11/20040502)
MIME-Version: 1.0
To: Martin Kuria <martinkuria@hotmail.com>
Cc: olly@lfix.co.uk, middink@indo.net.id, pgsql-sql@postgresql.org
Subject: Re: SUM() & GROUP BY
References: <Sea2-F17FNpyLAPDVXL0000f045@hotmail.com>
In-Reply-To: <Sea2-F17FNpyLAPDVXL0000f045@hotmail.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Martin Kuria wrote:
> 
> Here is what Division table contains:
> 
> SELECT * FROM ser_divisions;
> 
> divisions_name | divisions_id
> ---------------------------------------
> DEE                   |   3131
...


> 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;

> divisions_name | pd_geo  | count
> -----------------------------------------------
> DEE                 |   1         |  9
> DEE                 |   2         |  2
> DEE                 |   3         |  6
> DEE                 |   4         |  5

SELECT
   d.divisions_name,
   s.pd_geo,
   COUNT(s.pd_geo)
FROM
   ser_divisions d,
   ser s
WHERE
   d.divisions_id = s.ser_divisions
GROUP BY
   d.divisions_name, s.pd_geo
ORDER BY
   d.divisions_name, s.pd_geo
;

It's called a join, and any good SQL book should cover it.

-- 
   Richard Huxton
   Archonet Ltd