Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UR8Zn-0000x0-AY for pgsql-sql@arkaria.postgresql.org; Sat, 13 Apr 2013 22:05:39 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UR8Zm-0002mQ-3a for pgsql-sql@arkaria.postgresql.org; Sat, 13 Apr 2013 22:05:38 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UR8Zl-0002mK-2H for pgsql-sql@postgresql.org; Sat, 13 Apr 2013 22:05:37 +0000 Received: from mail-ia0-x232.google.com ([2607:f8b0:4001:c02::232]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UR8Zi-00085Z-9i for pgsql-sql@postgresql.org; Sat, 13 Apr 2013 22:05:36 +0000 Received: by mail-ia0-f178.google.com with SMTP id w21so1551933iac.23 for ; Sat, 13 Apr 2013 15:05:33 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:x-received:in-reply-to:references:date:message-id :subject:from:to:cc:content-type; bh=n+oqoc6xR6fcTV30Q0xEhblN7YE9nuxuSpKMerYKrQQ=; b=UKmAQLZ2TloDRc3J86xRBVG7oBeQ5aByfbDbYaeIYDuh2s5XIwd0jih2efckAkKGCb KkxwTj95MClGwSxgfcloDHybr/vGbhYasESdAlCsWqQMJlw9U3zvioMlRb1VYT//1Xif RGO9AsjXjZh1SdbIC2epEY7a1gq4Lf8e6DvB85tRUehgmxGd4B8PYgPXP2sATchIZUUt ONG3+xP3GlIq3cToazpd2wZ6OZP+r/jcfJMiJTCKbK8kWdnXhGjWqQjGKiK+Ee6NOVCw S3TmjlxqORwSo9ywzfLjPrO2yKrPqogCemM+StNAfwHO0du7LCSs24oW97gDrc/+QTFD hUbQ== MIME-Version: 1.0 X-Received: by 10.50.173.102 with SMTP id bj6mr2178055igc.16.1365890733044; Sat, 13 Apr 2013 15:05:33 -0700 (PDT) Received: by 10.64.93.130 with HTTP; Sat, 13 Apr 2013 15:05:32 -0700 (PDT) In-Reply-To: <1365823755894-5752078.post@n5.nabble.com> References: <1365823565069-5752077.post@n5.nabble.com> <1365823755894-5752078.post@n5.nabble.com> Date: Sat, 13 Apr 2013 18:05:32 -0400 Message-ID: Subject: Re: pivot query with count From: Tony Capobianco To: David Johnston Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=e89a8f83a6aff1fe4804da453774 X-Pg-Spam-Score: -0.6 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --e89a8f83a6aff1fe4804da453774 Content-Type: text/plain; charset=ISO-8859-1 Thank you very much for your response. However, I'm unclear what you want me to substitute for sum(...)? select '1' as "num_ads", sum(...) from (select a.userid from user_event_stg2 a, user_region b where a.userid = b.userid and b.region_code = 1000 and a.messagetype = 'impression' group by a.userid having count(a.userid) = 1) group by num_ads; I was able to eliminate that sub-select per your recommendation. That makes things a bit easier. Thanks. On Fri, Apr 12, 2013 at 11:29 PM, David Johnston wrote: > My prior comment simply answers your question. You likely can rewrite > your > query so that a separate grouping layer is not needed (or rather the group > by would exist in the main query and you minimize the case/sub-select > column > queries and use aggregates and case instead). > > David J. > > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/pivot-query-with-count-tp5752072p5752078.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > --e89a8f83a6aff1fe4804da453774 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
Thank you very much for your response. However, I'm unclear what y= ou want me to substitute for sum(...)?
=A0
select '1' as "num_ads", sum(...)
from
(select= a.userid from
user_event_stg2 a, user_region b
where a.userid =3D b.= userid
and b.region_code =3D 1000
and a.messagetype =3D 'impressi= on'
group by a.userid having count(a.userid) =3D 1)
group by num_ads;

I was able to eliminate that sub-select per your recommendation.= =A0 That makes things a bit easier.
=A0
Thanks.

=A0
On Fri, Apr 12, 2013 at 11:29 PM, David Johnston= <polobo@yahoo.com> wrote:
My prior comment simply answers your = question. =A0 You likely can rewrite your
query so that a separate group= ing layer is not needed (or rather the group
by would exist in the main query and you minimize the case/sub-select colum= n
queries and use aggregates and case instead).

David J.

<= br>

--
View this message in context: http://postgresql.1045698.n5.nabble.com/pivot-query-with-count= -tp5752072p5752078.html
Sent from the PostgreSQL - sql mailing list archive at Na= bble.com.


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make chang= es to your subscription:
= http://www.postgresql.org/mailpref/pgsql-sql

--e89a8f83a6aff1fe4804da453774--