Re: join/group/count query.

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(at)myemma(dot)com>
software development
emma(r)