From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: join/group/count query.
Date: Wed, 20 Dec 2006 10:12:25 +0000
User-Agent: KMail/1.9.5
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200612201012.25960.gary.stainburn@ringways.co.uk>

Hi folks.  I have the following query which works for me at the moment.  
However, o_model refers to a table stock_models which has one record for each 
model type. At the moment if I another record to the stock_models I have to 
amend the select.  Is it possible to make this automatic by joining the 
stock_models data somehow?

select o_p_id as key, o_p_id as p_id, p_name, count(o_p_id) as total,
   count (case when o_model = 5 then 1 else NULL end) as KA,
   count (case when o_model = 10 then 1 else NULL end) as Focus,
   count (case when o_model = 13 then 1 else NULL end) as C_Max,
   count (case when o_model = 16 then 1 else NULL end) as S_Max,
   count (case when o_model = 20 then 1 else NULL end) as Fiesta,
   count (case when o_model = 25 then 1 else NULL end) as Fusion,
   count (case when o_model = 30 then 1 else NULL end) as Mondeo,
   count (case when o_model = 35 then 1 else NULL end) as Galaxy,
   count (case when o_model = 40 then 1 else NULL end) as Ranger,
   count (case when o_model = 50 then 1 else NULL end) as Connect,
   count (case when o_model = 60 then 1 else NULL end) as Transit,
   count (case when o_model = 70 then 1 else NULL end) as Fiesta_Van
   from order_details 
   where date_trunc('month',CURRENT_DATE) = date_trunc('month',o_ord_date)
   group by o_p_id, p_name;
-- 
Gary Stainburn
 
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000