问题描述
Hello All,
我在DB中有下表,
ID Names
1 Sam
2 Khan
3 Julia
4 Khan
5 Wilson
6 Angeli
7 Maria
8 Khan
9 Sam
10 Sam
11 Julia
12 Maria
现在问题是我需要一个可以检索名字的SQL Server查询在DESCending订单中按名称分组及其频率(名称出现的次数)。例如对于上表,输出应该是这样的:
名称频率
Sam 3
Khan 3
Julia 2
Maria 2
Wilson 1
Angeli 1
我在查询中尝试了GroupBy和OrderBy子句但未能获得所需的结果。请帮我解决这个问题。
提前谢谢。
Tipu
Hello All,
I have the following table in the DB,
ID Names
1 Sam
2 Khan
3 Julia
4 Khan
5 Wilson
6 Angeli
7 Maria
8 Khan
9 Sam
10 Sam
11 Julia
12 Maria
Now the problem is that I need a SQL Server query which may retrieve Names Group by the Names with their frequencies(how many times a Name appears)in the DESCending order. For example for the above table the output should be like this:
Names Frequency
Sam 3
Khan 3
Julia 2
Maria 2
Wilson 1
Angeli 1
I have tried both GroupBy and OrderBy Clauses in query but failed to get the desired results. Please help me out of this.
Thank you in advance.
Tipu
推荐答案
; WITH ALL_NAME
AS (SELECT names AS NAMES,
COUNT(names) AS FREQUENCY
FROM test --YOUR_TABLE
GROUP BY names)
SELECT *
FROM ALL_NAME
ORDER BY FREQUENCY DESC
select name ,count(*) as frequency from testing group by name order by frequency desc
这篇关于SQL Server Group by和Order by在同一个查询中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!