Thread: SQL help - multiple aggregates

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

On Thu, Aug 18, 2016 at 10:56 AM,  <hamann.w@t-online.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.

Hello.


On 18.8.2016 10:56, hamann.w@t-online.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

CCing to the list ( if you are new to this list, messages come from
the sender address, you have to use "reply all" ( at least in my MUA,
web gmail ) to make your replies appear in the list ).

On Thu, Aug 18, 2016 at 3:03 PM,  <hamann.w@t-online.de> wrote:
> Hi Francisco,
> thanks a lot. I will give it a try later

Do it, and do not forget to try the straightforward solution ( sume of
cases ) given by Ladislav Lenart above.I normally prefer to do this
kind of things the way I pointed you because the queries are simpler
and normally only the first one takes time, and using count tends to
be the faster way to extract the relevant data ( the rest of my query,
after the first with, is just moving data around for pretty-printing (
or pretty-selecting ).

Francisco Olarte.