Count and Group By

O

orne30712

I have a dataset that looks like this:

CREATE TABLE Scores ([ID] IDENTITY(1,1), StudentID INT, SubjectID INT, Score
FLOAT)

I want to get the lowest score and the subject for each student. So the
results will be

Student 1 MATH 50
Student 2 ENG 60
Student 3 MATH 55
Student 4 MATH 57

This query only gives me the StudentID and Max Score but not the SubjectID:

SELECT StudentID, MAX(Score)
FROM Scores
GROUP BY StudentID

If I add the SubjectID, it gives me every subject for every student:

SELECT StudentID, SubjectID, MAX(Score)
FROM Scores
GROUP BY StudentID, SubjectID

Any idea?
TIA
 
M

Michel Walsh

You can use one of the four techniques presented at
http://www.mvps.org/access/queries/qry0020.htm



The easiest one is probably to do it in two steps. The first query get the
MIN (or MAX) per student (your first query). Then, make a second query,
bring the table and the query you just made, link the two through their
field StudentID and YourTable.Score-YourQuery.MinOfScore.


Sure, if Student1 has two scores of 50, and both minimum, they will both be
selected.



Hoping it may help,
Vanderghast, Access MVP
 
K

KARL DEWEY

Try this --
SELECT T.StudentID, T.SubjectID, T.Score
FROM Scores AS T
WHERE ((((SELECT COUNT(*)
FROM [Scores] T1
WHERE T1.StudentID = T.StudentID
AND T1.Score <= T.Score))=1))
ORDER BY T.StudentID, T.Score;
 
K

KARL DEWEY

Michel Walsh post reminded me that identical scores at the low end would not
be selected as they would both be ranked as 2nd. Below brings in 1st and 2nd
scores.

SELECT T.StudentID, T.SubjectID, T.Score
FROM Scores AS T
WHERE ((((SELECT COUNT(*)
FROM [Scores] T1
WHERE T1.StudentID = T.StudentID
AND T1.Score <= T.Score))<=2))
ORDER BY T.StudentID, T.Score;

--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
Try this --
SELECT T.StudentID, T.SubjectID, T.Score
FROM Scores AS T
WHERE ((((SELECT COUNT(*)
FROM [Scores] T1
WHERE T1.StudentID = T.StudentID
AND T1.Score <= T.Score))=1))
ORDER BY T.StudentID, T.Score;

--
KARL DEWEY
Build a little - Test a little


orne30712 said:
I have a dataset that looks like this:

CREATE TABLE Scores ([ID] IDENTITY(1,1), StudentID INT, SubjectID INT, Score
FLOAT)

I want to get the lowest score and the subject for each student. So the
results will be

Student 1 MATH 50
Student 2 ENG 60
Student 3 MATH 55
Student 4 MATH 57

This query only gives me the StudentID and Max Score but not the SubjectID:

SELECT StudentID, MAX(Score)
FROM Scores
GROUP BY StudentID

If I add the SubjectID, it gives me every subject for every student:

SELECT StudentID, SubjectID, MAX(Score)
FROM Scores
GROUP BY StudentID, SubjectID

Any idea?
TIA
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top