Forum

Posted by 泽鹏, Jan. 14, 2024, 7:19 p.m.

Question for 14. Good days and bad days

select sum(case when val>100 then 1 else 0 end) ,sum(case when val <=100 then 1 else 0 end)
from (
SELECT EXTRACT(Day from rental_ts               )   ,count(rental_id              ) val
FROM rental  
where EXTRACT(YEAR from rental_ts               )=2020 and EXTRACT(MONth from rental_ts               ) = 5
group by EXTRACT(Day from rental_ts               )    
) temp

why did not work?

 

Answers

It didn't work because you didn't count all the days in May 2020. Your inner query gives only for the dates that has rental information. You have join this inner query with dates table to get all the dates of May 2020 and then do the count for good days and bad days. Hope this helps :)

SQLPad user avatar

Abinaya (2)

Feb. 3, 2024, 11:55 a.m.