SQL Server Group by和Order by在同一个查询中 [英] SQL Server Group by and Order by in the same query

问题描述

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

现场演示

Table_1

Col1

A

A

A

B

B



选择col1，count（col1）来自table_1 group by col1



它给我的结果如



A 3

B 2

Table_1
Col1
A
A
A
B
B

select col1,count(col1) from table_1 group by col1

It gives me result like

A 3
B 2

这篇关于SQL Server Group by和Order by在同一个查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！