sql - How to count entities, factored in cases, when there's no objects satisfying the condition from join clause in SQL?

Question

I have a simple table with some data. I would like to write query, which returns "cas number" of objects, which have more "sent" rows, than "rent" rows. My query is attached below:

select 
    c1.cas 
from
    casdat c1 
join 
    casdat c2 
on 
    c1.cas=c2.cas
    and c1.type='sent' 
    and c2.type='rent'
group by 
    c1.cas
having 
    count(distinct c1.data) > count(distinct c2.data); 

The problem is, my query does not process a case, when some specific "cas" has few "sent" rows and 0 "rent" ones. How to include such a case for my query? I would like to keep the JOIN clause. I know i could write a subquery and count it like that:

select c1.cas,count(distinct c1.data) from casdat c1
where c1.type='sent'
group by c1.cas
having count(distinct c1.data)>
(select count(distinct c2.data) from casdat c2 where c2.type='rent' and
c2.cas=c1.cas);

I would like that my query works like the second one attached here, but i need to use join clause. How to write such a query?

标签： sql

Answer 1

使用带有having子句的聚合：

select c.cas
from casdat c
group by c.ca
having sum(case when c.type = 'sent' then 1 else 0 end) > sum(case when c.type = 'rent' then 1 else 0 end);