From: Erik Jones <erik@myemma.com>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: join/group/count query.
Date: Wed, 20 Dec 2006 09:46:51 -0600
Message-ID: <45895AEB.20501@myemma.com> (raw)
In-Reply-To: <1166612996.6369.282.camel@localhost.localdomain>
References: <200612201012.25960.gary.stainburn@ringways.co.uk>
<1166612996.6369.282.camel@localhost.localdomain>
Ragnar wrote:
> 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
>
And, I may be missing something, but I'm having a hard time
understanding why you have all of those select columns of the form:
count (case when o_model = 5 then 1 else NULL end) as KA,
Considering that that can only return 1 or 0, the case statement would do. Is it to avoid putting all of the column names in the group by clause? That's hackish and is as much or more typing.
With regards to what you are actually trying to do, giving us your table definitions and what you are trying to achieve would help a lot more than just telling us the problem you are having. The column names in your query are in no way descriptive and tell us nothing about your actual table structure.
--
erik jones <erik@myemma.com>
software development
emma(r)
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: erik@myemma.com, gary.stainburn@ringways.co.uk
Subject: Re: join/group/count query.
In-Reply-To: <45895AEB.20501@myemma.com>
* 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