I was doing some SQL practice problems and found a problem, one of whose alternate solutions perplexes me.
The problem is simplified as follows:
Show the province id's that have more patients identified as 'M' than 'F'.
The table named 'patients' has relevant columns patient_id, province_id, and gender (either 'M' or 'F').
I don't understand this given solution, even though it works on the website that runs SQL (the version is unknown on the site, which is www.sql-practice.com for reference).
SELECT province_id
FROM patients
GROUP BY province_id
HAVING SUM(gender = 'M') > SUM(gender = 'F')
In the HAVING portion of the solution, why does this program allow you to specify a condition inside the parenthesis of the aggregate? Is this a particular version of SQL that I'm not used to? I attempted this kind of construction on my desktop app SQL Server using a different dataset and I get a syntax error, which is what I thought would occur if you tried to set a conditional in an aggregate function.
Due to the nature of my question, an attempt to solve this problem is not applicable because I am seeking clarity on an alternative solution that apparently works but has no documentation anywhere, nor can I find anyone asking about it on StackOverflow.
