count query

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Proogeren
    New Member
    • May 2007
    • 36

    #1

    count query

    Hello
    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
  • mwasif
    Recognized Expert Contributor
    • Jul 2006
    • 802

    #2
    Use the corrent column names with correct case.

    Code:
    SELECT m.MovieName, COUNT(d.movieID) AS times_downloaded from Movie as m
    LEFT JOIN Downloaded d ON m.movieID = d.movieID
    GROUP BY m.movieID

    Comment

    Working...