SQL question : JOIN and COUNT(*) in same query

Posted on 2005-03-30
Medium Priority
Last Modified: 2009-12-16
I need SQL for the following or i need to know if it is not possible, i know i can do it in two seperate queries but this isnt really an option.

I have two tables 'Users' and 'Ratings' I need to select each user from 'Users' and then order them depending on how many related records there are in the 'Ratings' table. eg. how many time they have been rated. They are related by the fields User.GUID to Ratings.RatingTo.


Question by:coolnicks
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 35

Expert Comment

by:David Todd
ID: 13666546

Off the top of my head try this ...

select user.GUID, count( * )
from user
inner join ratings
on User.GUID = Ratings.RatingTo
group by user.GUID
order by count( * ) desc

a) you may need to edit this.
b) Adding additional columns complicates the group by functioning and may give wrong results. Sometimes the best way of doing this is to use the above in a subquery to return the count ... ask if you need this sketched out for you.


LVL 13

Accepted Solution

KarinLoos earned 375 total points
ID: 13666554
select users.GUID,
from users  
join ratings on ratings.ratingsto = users.gui
order by users.guid

Author Comment

ID: 13697940
thank you both.

dtodd's solution returned the count corrently, but also returned 1, even if there was no matching record

KarinLoos's also worked, but didnt not return anything if there was no matching record, i ran with this code in the end but had to add the group by aswell.

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

752 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