Postgresql count substring with a "where" clause

Question

I am trying to count a substring, while matching different values from another column. The following statement gives me a syntax error on the where clause.

Is the following even possible, and what is the correct syntax?

select address,
       datacenter,
       ifdesc,
       count(substring(ifdesc, 'Ethernet0/*') where ifadminstatus = '1' and ifoperstatus = '1')  over (partition by address) mod0_uu,
       count(substring(ifdesc, 'Ethernet0/*') where ifadminstatus = '2') over (partition by address) mod0_ad
  from ifstatus;

rfusca · Accepted Answer · 2010-06-29 03:17:56Z

2

Something like this?

select address,
       datacenter,
       ifdesc,
       count(case when ifadminstatus = '1' and ifoperstatus = '1' then substring(ifdesc, 'Ethernet0/*') else null end )  over (partition by address) mod0_uu,
       count(case when ifadminstatus = '2' then substring(ifdesc, 'Ethernet0/*') else null end ) over (partition by address) mod0_ad
  from ifstatus  WHERE ifadminstatus in ('1','2');

answered Jun 29, 2010 at 3:17

rfusca

7,7752 gold badges32 silver badges34 bronze badges

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

Collectives™ on Stack Overflow

Postgresql count substring with a "where" clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related