SQL help - multiple aggregates

From: hamann(dot)w(at)t-online(dot)de
To: pgsql-general(at)postgresql(dot)org
Subject: SQL help - multiple aggregates
Date: 2016-08-18 08:56:24
Message-ID: wolfgang-1160818105624.A021691@noten19.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Hi,

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

The only way i managed to achieve that was

select distinct custid into temp table cv1 from cv;
alter table cv1 add column c415 int;
update cv1 set c415 = part.c415 from
(select custid, count(vendid) as c415 from cv where vendid = 415 group by custid) part
where cv1.custid = part.custid;
and repeating the process for every column requested

Is there a better way (by creating an aggregate function, perhaps)

Regards
Wolfgang

Responses

Browse pgsql-general by date

  From Date Subject
Next 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
Previous Message Silk Parrot 2016-08-18 06:02:53 Unique constraint on field inside composite type.