Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 46CFF9F9DAD for ; Wed, 20 Dec 2006 07:08:10 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 89677-06 for ; Wed, 20 Dec 2006 07:08:04 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mx2.ipf.is (scania.ipf.is [85.197.192.147]) by postgresql.org (Postfix) with ESMTP id 7F1FC9F9C35 for ; Wed, 20 Dec 2006 07:08:03 -0400 (AST) Received: (qmail 8728 invoked by uid 1010); 20 Dec 2006 11:04:49 +0000 Received: from 88.149.106.19 by scania (envelope-from , uid 1008) with qmail-scanner-1.25-st-qms (clamdscan: 0.88/1235. spamassassin: 3.1.1. perlscan: 1.25-st-qms. Clear:RC:1(88.149.106.19):SA:0(-2.0/4.5):. Processed in 2.650981 secs); 20 Dec 2006 11:04:49 -0000 X-Antivirus-IPF-Mail-From: gnari@hive.is via scania X-Antivirus-IPF: 1.25-st-qms (Clear:RC:1(88.149.106.19):SA:0(-2.0/4.5):. Processed in 2.650981 secs Process 8721) Received: from dsl-149-106-19.hive.is (HELO ?192.168.1.33?) (88.149.106.19) by mx2.ipf.is with SMTP; 20 Dec 2006 11:04:46 +0000 Subject: Re: join/group/count query. From: Ragnar To: Gary Stainburn Cc: pgsql-sql@postgresql.org In-Reply-To: <200612201012.25960.gary.stainburn@ringways.co.uk> References: <200612201012.25960.gary.stainburn@ringways.co.uk> Content-Type: text/plain; charset=ISO-8859-1 Date: Wed, 20 Dec 2006 11:09:56 +0000 Message-Id: <1166612996.6369.282.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.8.1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/155 X-Sequence-Number: 27111 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