Hans_Christian
asked on
How do I sort/order a group by count in Access?
Hi.
Im using Access 2010, working in an ADP-file. I have extracted some tables from a sql-database. Then I have made a View where want to extract som data. What I have to do is make a view of number of students and years grouped by year and sorted by count of student each year but it doesnt work.
If you look at the View I have made you can see that it is grouped by Immaar (means year) but not sorted by Count in descending order (it is count of studentID. Each student has his/her own ID).
What i want to have in view is this:
2007 6
2008 6
2005 4
2006 4
2002 2
2004 2
2009 1
My code is this:
SELECT TOP 100 PERCENT Immaar, COUNT(StudentID) AS Number
FROM dbo.Student
GROUP BY Immaar
ORDER BY COUNT(StudentID) DESC
Screenshot.jpg
Im using Access 2010, working in an ADP-file. I have extracted some tables from a sql-database. Then I have made a View where want to extract som data. What I have to do is make a view of number of students and years grouped by year and sorted by count of student each year but it doesnt work.
If you look at the View I have made you can see that it is grouped by Immaar (means year) but not sorted by Count in descending order (it is count of studentID. Each student has his/her own ID).
What i want to have in view is this:
2007 6
2008 6
2005 4
2006 4
2002 2
2004 2
2009 1
My code is this:
SELECT TOP 100 PERCENT Immaar, COUNT(StudentID) AS Number
FROM dbo.Student
GROUP BY Immaar
ORDER BY COUNT(StudentID) DESC
Screenshot.jpg
ASKER
That didn't do it. The result order is the exact same as the screenshot above. What happens is that Access automatically changes the "2" back to "COUNT(StudentID)" when you click outside the code.
weird, ok try this
SELECT Immaar, Number FROM (
SELECT TOP 100 PERCENT Immaar, COUNT(StudentID) AS Number
FROM dbo.Student
GROUP BY Immaar
) AS DATA
ORDER BY Number DESC
SELECT Immaar, Number FROM (
SELECT TOP 100 PERCENT Immaar, COUNT(StudentID) AS Number
FROM dbo.Student
GROUP BY Immaar
) AS DATA
ORDER BY Number DESC
ASKER
Nothing happens. The sort order is still the same. The code changes to this, when clicking outside the code-area:
SELECT TOP 100 PERCENT Immaar, Number
FROM (SELECT TOP 100 PERCENT Immaar, COUNT(StudentID) AS Number
FROM dbo.Student
GROUP BY Immaar) DATA
ORDER BY Number DESC
It added "TOP 100 PERCENT" in the top and changed "AS DATA" to "DATA". I can see what your intentions were. Putting a table around the original. But it didnt do it either.
SELECT TOP 100 PERCENT Immaar, Number
FROM (SELECT TOP 100 PERCENT Immaar, COUNT(StudentID) AS Number
FROM dbo.Student
GROUP BY Immaar) DATA
ORDER BY Number DESC
It added "TOP 100 PERCENT" in the top and changed "AS DATA" to "DATA". I can see what your intentions were. Putting a table around the original. But it didnt do it either.
ok, my bad I have not read that this is a View, lets try this one
SELECT * FROM (
SELECT TOP 99 PERCENT Immaar, COUNT(StudentID) AS Number
FROM dbo.Student
GROUP BY Immaar
GROUP BY COUNT(StudentID) DESC
) AS DATA
SELECT * FROM (
SELECT TOP 99 PERCENT Immaar, COUNT(StudentID) AS Number
FROM dbo.Student
GROUP BY Immaar
GROUP BY COUNT(StudentID) DESC
) AS DATA
Sorry again the second GROUP BY is actually a SORT BY
ASKER
Now I get an error message. Error in group by clause. Im not sure it will accept to group by Count(StudentID). I tried that earlier with no luck. And now its sudden 99 Percent and not 100?
The code changes to this when clicking outside after pasting your code:
SELECT *
FROM (SELECT TOP 99 PERCENT Immaar, COUNT(StudentID) AS Number
FROM dbo.Student
GROUP BY Immaar
GROUP BY COUNT(StudentID) DESC) AS DATA
The code changes to this when clicking outside after pasting your code:
SELECT *
FROM (SELECT TOP 99 PERCENT Immaar, COUNT(StudentID) AS Number
FROM dbo.Student
GROUP BY Immaar
GROUP BY COUNT(StudentID) DESC) AS DATA
try this:
SELECT TOP (99.999999) PERCENT Immaar, COUNT(StudentID) AS Number
FROM dbo.Student
GROUP BY Immaar
ORDER BY COUNT(StudentID) DESC
SELECT TOP (99.999999) PERCENT Immaar, COUNT(StudentID) AS Number
FROM dbo.Student
GROUP BY Immaar
ORDER BY COUNT(StudentID) DESC
This works fine for me, it produces the exact output you requrested
...or am I missing something...
SELECT Student.Immaar, Count(Student.StudentID) AS [Number]
FROM Student
GROUP BY Student.Immaar, Student.Immaar
ORDER BY Count(Student.StudentID) DESC , Student.Immaar;
JeffCoachman
...or am I missing something...
SELECT Student.Immaar, Count(Student.StudentID) AS [Number]
FROM Student
GROUP BY Student.Immaar, Student.Immaar
ORDER BY Count(Student.StudentID) DESC , Student.Immaar;
JeffCoachman
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If this is SQL Server you should not be using TOP 100 PERCENT ... ORDER BY it is simply pointless. SQL Server is smart enough to see that you want all the rows and skip the sort entirely.
And if it is not SQL Server than perhaps you should not be posting in this zone...
And if it is not SQL Server than perhaps you should not be posting in this zone...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
gamarrojqq: That did it! Thanks. The workaround.
Ztinel: Thanks for the link read it with interest.
acperkins. Thanks for that information.
Ztinel: Thanks for the link read it with interest.
acperkins. Thanks for that information.
Try this
SELECT TOP 100 PERCENT Immaar, COUNT(StudentID) AS Number
FROM dbo.Student
GROUP BY Immaar
ORDER BY 2 DESC