From: | Francisco Olarte <folarte(at)peoplecall(dot)com> |
---|---|
To: | hamann(dot)w(at)t-online(dot)de |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: SQL help - multiple aggregates |
Date: | 2016-08-18 11:05:33 |
Message-ID: | CA+bJJbx0ebCCNXq-6+RRSkYofyuUz2HpzT8Q-Vwy=eBHUp2WaA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Aug 18, 2016 at 10:56 AM, <hamann(dot)w(at)t-online(dot)de> wrote:
> I have a table cv with custid and vendid columns. Every entry represents the purchase of a product
> available from a specific vendor.
> Now, for a set of "interesting" vendors, I would like to select a new table
> custid, c415, c983, c1256
> based upon part queries
> select custid, count(vendid) as c415 from cv where vendid = 415 group by custid
....
Divide and conquer, first you get the raw data ( so you have what you
need as 'vertical' tagged columns ): ( beware, untested )...
with raw_data as (
select
custid, vendid, count(*) as c
from cv
where vendid in (415,983,1256)
group by 1,2;
)
Then put it in three columns ( transforming it into diagonal matrix ):
, column_data as (
select
custid,
case when vendid=415 then c else 0 end as c415,
case when vendid=983 then c else 0 end as c983,
case when vendid=1256 then c else 0 end as c1256
from raw_data
)
and then group then ( putting them into horizontal rows ):
select
custid,
max(c415) as c415,
max(c983) as c983,
max(c1256) as c1256
from column_data group by 1;
Note:
I used 0 in else to get correct counts for the case where not al
vendids are present. If you prefer null you can use it, IIRC max
ignores them.
Francisco Olarte.
From | Date | Subject | |
---|---|---|---|
Next Message | pinker | 2016-08-18 11:32:12 | Sequential vs. random values - number of pages in B-tree |
Previous Message | Vik Fearing | 2016-08-18 10:05:43 | Re: Re: Easiest way to compare the results of two queries row by row and column by column |