Re: SQL help - multiple aggregates

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.