--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 "人數(shù)合計",

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

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

FROM test1 GROUP BY city ORDER BY gender

報錯: test1列”。“性別”在ORDER BY子句中是無效的，因?yàn)樗葲]有包含在聚合函數(shù)中，也沒有包含在GROUP BY子句中。

想知道為什么及如何正確寫出該問題的答案