coder_1432 coder_1432 - 2 years ago 144
SQL Question


If I want to know the movie name and its sales total, can I use the query below?

Movie.Name, Movie.Sales, FROM Movie GROUP BY Movie.Name HAVING SUM (Movie.Sales)>12000 0 ORDER BY SUM(Movie.Sales)DESC;

I know it’s best to write

Movie.Name, Movie.Sales, FROM Movie GROUP BY Movie.Name HAVING Movie.Sales>120000 ORDER BY Movie.Sales DESC;

I was just wondering if the first one would still be considered a good answer in a test. BTW both queries give the same thing but SUM is not needed since Movie.Sales already has the total. Do you think it’s a bad answer?

Answer Source

No, the first query will not work because it has an error. You are doing a GROUP BY on the Movie.Name field, but attempting to include a non-grouped field in your query without an aggregate function. Anywhere you want to use the Movie.Sales field, you'll have to use it in conjunction with the SUM() function.

SELECT Movie.Name, SUM(Movie.Sales) Sales
FROM Movie
GROUP BY Movie.Name
HAVING SUM(Movie.Sales)>120000

The above is the corrected version of your first query, but your second query is definitely correct, and probably best practice since the table is already aggregated.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download