By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
463,191 Members | 626 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 463,191 IT Pros & Developers. It's quick & easy.

count query

P: 36
I was wondering how I can make query on 3 tables and sort on a count statement

E.g If
I have one table Person
I have one table Movie
I have one table Downloaded

If a person download a movie this is stored in the downloaded table. The download table looks like this
DownloadedId (PK)
PersonId (FK)
MovieId (FK)

Now if I want get all movies that has been downloaded and sort them by the number of times they have been downloaded

So if my table contains
DownloadedId PersonId MovieId
1 1 1
2 2 1
3 3 2
4 4 3
5 1 3
6 2 3

here I would want to write out so that
movieID 3 is first (downloaded 3 times)
movieID 1 is second (downloaded 2 times)
movieID 2 is last (downloaded 1 times)

My query looks like this
Select * from Person as p, Movie as m, Downloaded as d
Where p.personID = d.personID
And m.movieID = d.movieID

Help is appreciated
Jul 2 '07 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 801
Use the corrent column names with correct case.

Expand|Select|Wrap|Line Numbers
  1. SELECT m.MovieName, COUNT(d.movieID) AS times_downloaded from Movie as m
  2. LEFT JOIN Downloaded d ON m.movieID = d.movieID
  3. GROUP BY m.movieID
Jul 2 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.