Re: [GENERAL] SQL help - multiple aggregates

[Francisco Olarte]

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,   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

Re: [GENERAL] SQL help - multiple aggregates

[David G. Johnston]

On Thu, Aug 18, 2016 at 4:56 AM,  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.​

Re: [GENERAL] SQL help - multiple aggregates

[Ladislav Lenart]

Hello.


On 18.8.2016 10:56, haman...@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

Re: [GENERAL] SQL help - multiple aggregates

[Francisco Olarte]

On Thu, Aug 18, 2016 at 10:56 AM,   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

[GENERAL] SQL help - multiple aggregates

[hamann . w]

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




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