GROUP BY useage

My query:

SELECT TOP 10 stats_page
FROM site_stats
GROUP BY stats_page
ORDER BY idDESC

Produces the error:
Column "site_stats.id" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Can someone please explain why this does not work? And perhaps offer a solution?

Thanks

From the error message we can see your query conflict with a rule of using ORDER BY clause in T-SQL. So you can write a query like this:

SELECT TOP 10 stats_page
FROM site_stats
GROUP BY stats_page,id
ORDER BY idDESC

|||

Because, you are asking for the top 10 results of stats_page.

It's akin to saying, ok, make me a list of the top 10 most expensive cars in the world. Now sort that list by color.

Each car can come in one of many colors (As each of your stats_page can have multiple id's), but what color (id) are you referring to for each car? Did you want to sort by the least color (alphabetically) that each car is available in? So if the ferrari is only available in green and red, it would come after a mercedes benz that is available in black, purple, and yellow (Since the least color for ferrari is green, and the least color for mercedes is black)? Or did you want to sort by the highest color (alphabetically) so that the ferrari (red) comes before mercedes (yellow)? Or sort by the number of colors each car is available in (2 for ferrari, 3 for mercedes)?

You must choose which ID is to represent each stats_page since there are multiple ID's for each stats_page (If there is not multiple, then you shouldn't be using GROUP BY). You could use an ORDER BY MIN(id) if you want to order by the lowest id for each stats_page, or ORDER BY MAX(id) if you want to order by the highest id, or COUNT(id) for the number of id's, etc.

|||

Iori_Jay:

From the error message we can see your queryconflict with a rule of using ORDER BY clause in T-SQL. So you canwrite a query like this:

SELECT TOP 10 stats_page
FROM site_stats
GROUP BY stats_page,id
ORDER BY idDESC

Even if I use that, I get a syntax error on the "GROUP BY stats_page,id" line.

Motley:

You must choose which ID is to represent each stats_page since there are multiple ID's for each stats_page (If there is not multiple, then you shouldn't be using GROUP BY). You could use an ORDER BY MIN(id) if you want to order by the lowest id for each stats_page, or ORDER BY MAX(id) if you want to order by the highest id, or COUNT(id) for the number of id's, etc.

soo...my new query would be...? Sorry but I'm a little lost here xD|||

lori_Jay:

Motley:

You must choose which ID is to represent each stats_page since there are multiple ID's for each stats_page (If there is not multiple, then you shouldn't be using GROUP BY). You could use an ORDER BY MIN(id) if you want to order by the lowest id for each stats_page, or ORDER BY MAX(id) if you want to order by the highest id, or COUNT(id) for the number of id's, etc.

soo...my new query would be...? Sorry but I'm a little lost here xD

Something like this:

SELECT TOP 10 stats_page, MIN(id)
FROM site_stats
GROUP BY stats_page
ORDER BY MIN(id)DESC

|||I would need a small sample of what your site_stats table data looks like, and what result you want the query to give in order to give you an answer. I'm guessing the query you want isn't really what you have, and I'm unsure of what it is that you want.|||Yay, I'm starting to make progress.

The query now works using:
SELECT TOP 10 stats_page, MIN(id)
FROM site_stats
GROUP BY stats_page
ORDER BY MIN(id) DESC

And to answer motley's question. The site_stats table contains:
id, stats_ip, stats_page, stats_date

All I want to be able to do (In this case) is to see the most frequently visited pages.
ie. I'd be expecting something like Default.aspx to be first as it's visited most frequently, followed by something like login.aspx. The amount of times the page has been accessed isn't really nesscary.|||SELECT TOP 10 stats_page,COUNT(*)
FROM site_stats
GROUP BY stats_page
ORDER BY COUNT(*) DESC|||That works perfectly.

Thanks every much for all your help :)