CODEWITHSUNDEEP
mysqlsql
Rick D
Rick D

Reputation: 139

Query Count, sub Query Average

I'm trying to query a small table and get the number of rows with a specific genre, and display the average price of the items in that genre, my code is below, but isn't displaying the correct results

SELECT Movie_Genre, COUNT(*) as COUNT 
FROM(
SELECT Movie_Genre,
    AVG(Movie_Cost) 'Average Price'
FROM `indemoviedb`.`movie`) AS T
GROUP BY Movie_Genre;

Any pointers as to where i'm going wrong here, when this code is run, i get a table with 2 columns and one row

Thanks

Upvotes: 0

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

You don't need a subquery for this:

SELECT Movie_Genre,
       AVG(Movie_Cost) as Average_Price,
       COUNT(*) as Num_Movies
FROM `indemoviedb`.`movie` m
GROUP BY Movie_Genre;

Notes:

  • Don't use single quotes for column aliases. That just leads to confusion and bugs.
  • Using a table alias is good. Using a table alias that is an abbreviation for the table name is much better.

Upvotes: 1

Related Questions