--QUESTION 1: Count male, female and total people of each city

CREATE TABLE test1 ([city] varchar(10), [gender] char(1))

INSERT INTO test1 ([city], [gender])

VALUES ('Delhi', 'M')

,('Delhi', 'F')

,('Delhi', 'M')

,('Delhi', 'M')

,('Pune', 'F')

,('Pune', 'M')

,('Pune', 'F')

,('Pune', 'F')

,('Banglore', 'F')

,('Banglore', 'F')

;

答案:

SELECT city,COUNT(*)AS "人数合计",

SUM(CASE WHEN gender='M' THEN 1 ELSE 0 END)AS "女士人数",

SUM(CASE WHEN gender='F' THEN 1 ELSE 0 END) AS "男士人数"

FROM test1 GROUP BY city ORDER BY gender

报错: test1列”。“性别”在ORDER BY子句中是无效的，因为它既没有包含在聚合函数中，也没有包含在GROUP BY子句中。

想知道为什么及如何正确写出该问题的答案