From: Ragnar <gnari@hive.is>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: join/group/count query.
Date: Wed, 20 Dec 2006 11:09:56 +0000
Message-ID: <1166612996.6369.282.camel@localhost.localdomain> (raw)
In-Reply-To: <200612201012.25960.gary.stainburn@ringways.co.uk>
References: <200612201012.25960.gary.stainburn@ringways.co.uk>
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
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: gnari@hive.is, gary.stainburn@ringways.co.uk
Subject: Re: join/group/count query.
In-Reply-To: <1166612996.6369.282.camel@localhost.localdomain>
* 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