CODEWITHSUNDEEP
sql
Aaron
Aaron

Reputation: 1455

Group by - Apply different filters for different groups

Query 1 - 

select rend, pay, count(*) from payer
where pay = '1'
group by rend, pay

Query 2 -

select rend, count(*) from payer
where pay = '2'
or 
(
pay = '3' AND model_code IN ( 'MGD', 'MEDI' )
)
group by rend

Combined query -

If I combine the queries like below, I get the correct counts for the first query because it does not have any additional filters. How do I apply the filters in the second query to the below query so they don't apply to pay_type = 1 which is used in Query 1. I am basically looking to combine these two queries into a single query, so I don't have to write multiple cte's for every pay that has a where clause.

select rend, pay, count(*) from payer
group by rend, pay

Upvotes: 0

Views: 139

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

How about simply doing this?

select rend, max(pay), count(*) from payer
where pay in ('1', '2') or
      (pay = '3' and model_code in ( 'MGD', 'MEDI' )
      )
group by rend;

It is unclear what you want for the second column for the second query.

Upvotes: 0

Zynon Putney II
Zynon Putney II

Reputation: 695

select rend, pay, count(*)
from payer
where case when pay = '1' then 1
           when pay = '2' then 1
           when pay = '3' and model_code in ('MGD', 'MEDI') then 1 
         = 1
group by rend, pay;

Upvotes: 1

Related Questions