• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 601
  • Last Modified:

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

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.

Cheers

Nick
0
coolnicks
Asked:
coolnicks
1 Solution
 
David ToddSenior DBACommented:
Hi,

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

Notes:
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.

Regards
  David

0
 
KarinLoosCommented:
select users.GUID,
         count(ratings.ratingsto)
from users  
join ratings on ratings.ratingsto = users.gui
order by users.guid
0
 
coolnicksAuthor Commented:
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.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now