2 where conditions from 1 column SQL

Question

I am trying to see 2 conditional columns using the WHERE clause from 1 column.

For example:

I have a dataset where I have a long list of calls. I would like to see two different columns where the first column shows a count of all those calls who are equal or longer that 0 seconds and a second column that shows a count of all those calls that are equal or longer to 120 seconds.

That is my first query:

SELECT distinct year(date) Year,  monthname(date) as Month, count(calls) Leads
FROM database.calls
where duration >= 120
and year(date) = 2022
group by month(date)
order by max(date); 

second query: (The only difference it's on the 3rd line where duration is equal to 0)

SELECT distinct year(date) Year,  monthname(date) as Month, count(calls) Leads
FROM database.calls
where duration >= 0
and year(date) = 2022
group by month(date)
order by max(date); 

Expected Result:

Year	Month	Calls(0sec)	Calls(120sec)
2022	January	654	521
2022	February	895	465
2022	March	562	321

Answer 1

For obtaining conditional sums you can use conditional aggregation: specify a condition inside your aggregate function, so that it aggregates only the eligible values.

SELECT YEAR(date)                    AS Year_,  
       MONTHNAME(date)               AS Month_, 
       SUM(IF(duration >   0, 1, 0)) AS Calls0Sec,
       SUM(IF(duration > 120, 1, 0)) AS Calls120Sec
FROM database.calls
WHERE YEAR(date) = 2022
GROUP BY YEAR(date), MONTHNAME(date)
ORDER BY MAX(date); 

Note: you need to add every selected and non-aggregated field inside the GROUP BY clause, otherwise you can expect either DBMS errors or wrong data.