How do I query a table and get the ranking of records based off of multiple ranked records?

Posted on 2011-03-13
Last Modified: 2012-05-11
I have a table that has several records of ranks. The ranking can be 0-5.

So my table is like so...

record_ID |  project_ID  |  ranking  |  date |

1. record ID is just Auto Incremented
2. project_ID is a random number and can have multiple records of the same Project_ID
3. ranking can be 0-5 (0 = no points, 5 = Highest points).
4. And the date

I need for the query to COMBINE the records with the same project_ID and ORDER each combined record by the AVERAGE ranking (5 being first).

I've written some basic/intermediate SQL queries but this one is giving me some trouble.

Thank you,

Question by:brihol44

Expert Comment

ID: 35125526
select avg(ranking), project_id from mytable group by project_id
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35125529
did you mean that you want one record for each project with average ranking ??

Project Id  ranking


Select Project_Id , Sum(ranking)/count(ranking) from Tablename
group by project_id

if not
give sampe data and show us what result you need

Expert Comment

ID: 35125535
sorry forgot the order by clause

select avg(ranking) as avg_rank, project_id from mytable group by project_id order by avg_rank desc
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


Author Comment

ID: 35125676
Sorry but writing it out actually made it more clear for directions. I'll remember to do this in the future sorry if it's way off from what I was originally asking.


Accepted Solution

sihar86 earned 500 total points
ID: 35125781
SELECT project_ID, SUM(ranking), COUNT(1)*5
FROM tablename
WHERE ranking > 0
GROUP BY project_ID

Open in new window

since 0 would not count,
I add where ranking > 0
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35125783
try this
Select Project_Id , Sum(ranking) as with_point ,(count(Project_Id) *5 ) as out_of_point  from Tablename
group by project_id

Correction in your result
for project 3 have 2 records

so 5 out of 10

Expert Comment

ID: 35125887
SELECT project_ID, SUM(ranking), COUNT(project_ID)*5, date
FROM tablename
WHERE ranking > 0
GROUP BY project_ID

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL: Do I need CLUSTERED here? 13 59
sql help 2 46
delete the first occurence of a duplicate row in sql 5 38
Very Large data in MYSQL 7 74
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question