SQL help - multiple aggregates

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Aug 18, 2016 at 4:56 AM, <hamann.w@t-online.de> wrote:​

select custid, count(vendid) as c415 from cv where vendid = 415 group by
custid
​[...]

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

​You may find crosstab in the tablefuncs extension to be of use.

​
https://www.postgresql.org/docs/current/static/tablefunc.html

​David J.​

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general