Most Efficient Query - 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 2005 Forums

Transact-SQL (2005)

Most Efficient Query

Author

Topic

acxdotfm
Starting Member

2 Posts

Posted - 2008-10-28 : 23:32:27

I've had this problem numerous times, and I've yet to find the perfect solution.

Say I have a table where users state their favorite shape:

usernameField, favoriteShape

John, Circle
Sally, Circle
Joe, Circle,
Bob, Square
Jane, Square
Peter, Triangle
Dan, Oval

What's the best way to write a query that returns this:

Circle: 3
Square: 2
Triangle: 1
Oval: 1

Sorted that way. So a query such that it returns the most popular shape, with the shape and the count of each people that have that shape as the value.

Any experts have an idea of the most efficient way to do this?

Thanks in advance for your help!

acxdotfm
Starting Member

2 Posts

Posted - 2008-10-29 : 00:04:35

Never mind the solution is the GROUP BY clause.

SELECT [Shape], COUNT(*) AS [ShapeCount]
FROM [dbo].[ShapePreferences]
GROUP BY [Shape]
ORDER BY [ShapeCount] DESC

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-10-29 : 08:10:34

I cant check it but I don't think that will work because the alias happens after the order by.
If it doesn't work then try
order by count(*)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 08:13:57

quote:
Originally posted by LoztInSpace

I cant check it but I don't think that will work because the alias happens after the order by.
If it doesn't work then try
order by count(*)

nope you can use aliases in ORDER BY

ursangel
Starting Member

17 Posts

Posted - 2008-10-29 : 08:26:24

try this
SELECT [Shape], COUNT(shape) AS [ShapeCount]
FROM [dbo].[ShapePreferences]
GROUP BY [Shape]
ORDER BY [ShapeCount] DESC

Regards
Angel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources