Getting distinct results

[drizzage]

This one should be pretty simple. I know very little about SQL, and can't seem to get a handle on it. I'm generating a graph showing the age ranges of all users who have submitted records. My query retrieves the actual ages, as well as how many users of each age.

My current query:

SELECT count(age) as thisAge, age
FROM Users
GROUP BY age
ORDER BY age desc

The problem is that there are multiple records by each user, and I need to be able to group, or make distinct by each user so the the same user's age does not come up several times and skew the results. Each user is required to enter their age with each submission, and unfortunatly I cannot give each user a separate ID because of the nature of the application. This makes a table with a lot of redudant info that's difficult to work with.

Here is an over-simplified example of my table of users and their ages:

User1 55
User2 40
User2 40
User2 40
User3 60
User3 60
User4 55

This is the result I'm trying to return:

40 1
55 2
60 1

Thanks in advance for what I hope will be a very simple solution!

 

[PHV]

SELECT age, COUNT(DISTINCT user) AS thisAge
FROM Users
GROUP BY age
ORDER BY age DESC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[drizzage]

Thank you very much! I tried to stick the DISTINCT function in there but didn't know exactly how to incorporate it. This seems to have done the trick. Thanks again.