SQL Server Group by and Order by in the same query

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

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

Posted 3-Apr-14 3:07am

tipu khan jan

Add a Solution

5 solutions

Solution 5

SQL

; 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

Posted 3-Apr-14 13:21pm

Guruprasad.K.Basavaraju

Solution 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

Posted 3-Apr-14 3:22am

girishmeena

Solution 3

Refer and Have the Difference

http://www.thetricky.net/mySQL/GROUP%20BY%20vs%20ORDER%20BY[^]

http://sqlanddotnetdevelopment.blogspot.in/2011/06/difference-between-group-by-and-order.html[^]

Posted 3-Apr-14 3:56am

vsrikanth87

Solution 4

Here is the solution. This is tested.

SQL

create table test(
id int identity(1, 1), names nvarchar(80)
)

insert into  test values ('Sam')
insert into  test values ('Khan')
insert into  test values('Julia')
insert into  test values ('Khan')
insert into  test values ('Wilson')
insert into  test values ('Angeli ')
insert into  test values ('Maria')
insert into  test values ('Khan')
insert into  test values ('Sam')
insert into  test values ('Sam')
insert into  test values ('Julia')
insert into  test values ('Maria')

SQL

 SELECT names, 
         COUNT(names) TotalCount
    FROM test
GROUP BY names
  HAVING COUNT(names) > 1

Posted 3-Apr-14 6:32am

Er. Puneet Goel

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

King Fisher · Accepted Answer · 2014-04-03T03:16:00

Solution 1

SQL

select name ,count(*) as frequency from testing group by name order by frequency desc

live demonstration

Posted 3-Apr-14 3:16am

King Fisher

Updated 3-Apr-14 3:20am

v3

Comments

tipu khan jan 3-Apr-14 15:00pm

Thanks for yours response, but sorry I missed one another field, which is the DepartmentID. Now If I want to get the same results for a Specific DepartmentID I have to put the where clause, but it gives error. Can u please help me where to put the "where" cluase(Where DepartmentID = @DeptID)?

King Fisher 4-Apr-14 0:11am

select name ,count(*) as frequency from testing Where DepartmentID = @DeptID group by name order by frequency desc

tipu khan jan 4-Apr-14 7:31am

Thank you for your help, but the problem is still there .. It gives "no value given for one or more required parameters" exception, although m passing the parameter correctly .. can you please have a look at my code..

Here is my code ..

OleDbCommand CMD = new OleDbCommand();
OleDbDataAdapter DA = new OleDbDataAdapter();
CMD.Connection = Con;
CMD.CommandText = "select Names, COUNT(Names) as Frequency from Employees where DepartmentID = @DeptID Group By Names ORDER BY Frequency Desc";
CMD.Parameters.Add("@DeptID ", OleDbType.BigInt).Value = Convert.ToInt64(DeptID);
DA.SelectCommand = CMD;
DA.Fill(dt);

King Fisher 4-Apr-14 8:02am

error line?.check with breakpoint

King Fisher 4-Apr-14 8:05am

check with this :

CMD.CommandText = "select Names, COUNT(Names) as Frequency from Employees where DepartmentID ='"+DeptID+"' Group By Names ORDER BY Frequency Desc";

getting any error?

tipu khan jan 4-Apr-14 19:29pm

Thank you so much for ur help .. Still the same error .. But it worked this way ..

"SELECT * FROM (SELECT Names AS Names, COUNT(Names) AS FREQUENCY FROM Employees where DepartmentID = @DeptId GROUP BY Names) ORDER BY FREQUENCY DESC"