SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Group by with order by
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gsrinivas.
Yak Posting Veteran

56 Posts

Posted - 08/06/2013 :  08:12:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3653 Posts

Posted - 08/06/2013 :  08:20:58  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/06/2013 :  09:22:05  Show Profile  Reply with Quote
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
Go to Top of Page

gsrinivas.
Yak Posting Veteran

56 Posts

Posted - 08/06/2013 :  14:31:56  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/07/2013 :  01:20:34  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

gsrinivas.
Yak Posting Veteran

56 Posts

Posted - 08/07/2013 :  03:54:58  Show Profile  Reply with Quote
Hi Visakh,
Thank you so much, It is working fine.

Regards,
Srinivas
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/07/2013 :  06:32:27  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000