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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.


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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Mysql Crashing Intermittently 16 83
Oracle query output question 4 36
MySQL Grouping 2 25
MySqlDump not dumping triggers 1 19
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
Internet Business Fax to Email Made Easy - With  eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now