locked
Count multiple entries RRS feed

  • Question

  • I am trying to return a count of how many times multiple entries happen in a database, IE:  I have a Table: "VideoInventory", with VideoID, TitleID.  A Table "Title" with TitleID, MovieName, etc. I want the query to return the Movie Name and how many of each video I have, I can not figure out how to make the count return each seperate video without making 10000 different querys.  Thanks in advance for your help!

    Saturday, April 4, 2009 3:58 PM

Answers

  • Hi Kenneth,

    What you're looking to do is to group your query, and by doing so you can perform aggregations (in your case, count).  Try this:

    SELECT		t.MovieName, t.TitleID, COUNT(*) AS NumberOfTitlesInInventory
    FROM		Title AS t
    JOIN		VideoInventory vi ON t.TitleID = vi.TitleID
    GROUP BY	t.MovieName, t.TitleID
    Does this make sense?
    Aaron Alton | thehobt.blogspot.com
    Saturday, April 4, 2009 4:24 PM

All replies

  • Hi Kenneth,

    What you're looking to do is to group your query, and by doing so you can perform aggregations (in your case, count).  Try this:

    SELECT		t.MovieName, t.TitleID, COUNT(*) AS NumberOfTitlesInInventory
    FROM		Title AS t
    JOIN		VideoInventory vi ON t.TitleID = vi.TitleID
    GROUP BY	t.MovieName, t.TitleID
    Does this make sense?
    Aaron Alton | thehobt.blogspot.com
    Saturday, April 4, 2009 4:24 PM
  • Hi Kenneth,

    What you're looking to do is to group your query, and by doing so you can perform aggregations (in your case, count).  Try this:

    SELECT		t.MovieName, t.TitleID, COUNT(*) AS NumberOfTitlesInInventory
    
    FROM		Title AS t
    
    JOIN		VideoInventory vi ON t.TitleID = vi.TitleID
    
    GROUP BY	t.MovieName, t.TitleID
    
    
    Does this make sense?
    Aaron Alton | thehobt.blogspot.com

    I knew it had to be something so stupid simple... Man I need to pratice!  Thank you very much.
    Saturday, April 4, 2009 4:28 PM
  • We all learn at some point - no need to feel bad about asking a question!  Glad to help.
    Aaron Alton | thehobt.blogspot.com
    Saturday, April 4, 2009 4:42 PM