Re: join/group/count query.

On mið, 2006-12-20 at 10:12 +0000, Gary Stainburn wrote:
> 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;

if I understand correctly, you want one column in your output, 
for each row in the table table stock_models

you can do this with the crosstabN function in the contrib 
module 'tablefunc', or by making your own procedural language
function.

gnari