How to select a particular partition in Postgres when using partition by clause

Question

I have a query with the following structure -

select a.a1,b.b1,c.c1,d.d1,
        count(e.e1) over (partition by e.e2)
from a join b
 on a.aid = b.bid
 join c
 on b.bid = c.cid
 join e
 ......many other joins;

The problem is that I want to do something like count(e.e1) over (partition by e.e2 where e.e2 = 'mouse')

I mean I want to partition by the e2 column but consider one of the partitions.

For example, if e2 columns had the following values - "mouse", "cat" and "dog". Then the above query would give an output resembling the following -

a11 b11 c11 d11 4  -> record for "mouse" 
a11 b11 c11 d11 5  -> record for "cat"
a11 b11 c11 d11 7  -> record for "dog"

Now, I don't want the records for "cat" and "dog". I only want for "mouse". Any suggestions?

Can you add some sample data and the expected output based on that sample data? I don't really understand your question — user330315
– user330315, Commented Mar 6, 2015 at 15:31

Gordon Linoff · Accepted Answer · 2015-03-08 22:24:30Z

2

I think you want conditional aggregation:

select sum(case when e2 = 'mouse' then 1 else 0 end) over ()

This puts the number of "mouse"s on each row in the result set.

EDIT:

If it is based on the column, then you just want:

select count(*) over (partition by e2)

edited Mar 8, 2015 at 22:24

answered Mar 6, 2015 at 15:40

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Joe Love Over a year ago

Also, even easier is select sum((e2='mouse')::integer)

Kumar Vaibhav Over a year ago

Thanks for your reply. Please see the updated question for more clarification.

Kumar Vaibhav Over a year ago

If I do as it says in the edit, then the response would be contain rows for "cat", "dog" and "mouse"

Collectives™ on Stack Overflow

How to select a particular partition in Postgres when using partition by clause

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related