Re: SQL help - multiple aggregates

Hello.

On 18.8.2016 10:56, hamann(dot)w(at)t-online(dot)de wrote:
>
> 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)

Perhaps the following is what you need (not tested!):

SELECT
custid
, sum(CASE WHEN vendid = 415 THEN 1 ELSE 0 END) AS c415
, sum(CASE WHEN vendid = 983 THEN 1 ELSE 0 END) AS c983
, sum(CASE WHEN vendid = 1256 THEN 1 ELSE 0 END) AS c1256
FROM cv
GROUP BY 1

HTH,

Ladislav Lenart