Group by with order by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Group by with order by

Author

Topic

gsrinivas.
Yak Posting Veteran

56 Posts

Posted - 2013-08-06 : 08:12:05

Dear Team,

We have a table for Employees details(Emplid,Emplname,Age)
this table contains data as follows(sample)
50 years old employees are 50 rows of data
40 years old employees are 40 rows of data
20 years old employees are 30 rows of data

Now I would like to display all these group wise details in descending order.

could any one help me in this regards..

Thanks,
Srinivas

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-06 : 08:20:58

Do you mean something like this?

SELECT
	CASE
		WHEN age >= 50 THEN '50 and older'
		WHEN age >= 40 THEN '40 to 50'
		WHEN age >= 20 THEN '20 to 40'
		ELSE 'younger than 20'
	END AS AgeGroup,
	COUNT(*) AS Employees
FROM
	Employees
GROUP BY
	CASE
		WHEN age >= 50 THEN '50 and older'
		WHEN age >= 40 THEN '40 to 50'
		WHEN age >= 20 THEN '20 to 40'
		ELSE 'younger than 20'
	END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-06 : 09:22:05

Add this to order the final results


SELECT
	CASE
		WHEN age >= 50 THEN '50 and older'
		WHEN age >= 40 THEN '40 to 50'
		WHEN age >= 20 THEN '20 to 40'
		ELSE 'younger than 20'
	END AS AgeGroup,
	COUNT(*) AS Employees
FROM
	Employees
GROUP BY
	CASE
		WHEN age >= 50 THEN '50 and older'
		WHEN age >= 40 THEN '40 to 50'
		WHEN age >= 20 THEN '20 to 40'
		ELSE 'younger than 20'
	END
ORDER BY CASE AgeGroup
		WHEN '50 and older' THEN 1
		WHEN '40 to 50' THEN 2
		WHEN '20 to 40' THEN 3
		ELSE 4
	END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

gsrinivas.
Yak Posting Veteran

56 Posts

Posted - 2013-08-06 : 14:31:56

Dear Friends,
Thanks for your replies..

for better understanding I am updating column as salary instead of Age
Please note my input and outputs..

INPUT Tabel:
Eid---EName----Salary

1 Srinivas 3000
2 Bhaskar 2000
3 Ramesh 2000
4 Ganesh 5000
5 Sameer 2000
6 Venkat 4000
7 Gopal 2000
8 Janardhan 3000
.... and so on ...

Now the required output should be as follows..
Eid---EName----Salary

2 Bhaskar 2000
3 Ramesh 2000
5 Sameer 2000
7 Gopal 2000
1 Srinivas 3000
8 Janardhan 3000
4 Ganesh 5000
6 Venkat 4000
...and so on...

Here 2000 salary group has more rows..so it should be display first..and second group is 3000 salary..

and kindly note that the table has a lot of rows(in 10000 rows of data..so I don't prefer "select case" )

Now please let me know the query..

Thanks,
Srinivas.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-07 : 01:20:34

[code]
SELECT t.*
FROM Table t
INNER JOIN (SELECT Salary,COUNT(1) AS Cnt
FROM table
GROUP BY Salary
)t1
ON t1.Salary = t.Salary
ORDER BY Cnt DESC,Eid ASC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

gsrinivas.
Yak Posting Veteran

56 Posts

Posted - 2013-08-07 : 03:54:58

Hi Visakh,
Thank you so much, It is working fine.

Regards,
Srinivas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-07 : 06:32:27

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources