By legitcoding

2019-03-14 20:08:01 8 Comments

SELECT TOP(100) M.title, count(WH.movie_id)
FROM Movie AS M 
inner join WatchHistory AS WH ON M.movie_id = WH.movie_id
GROUP BY WH.movie_id, M.title, count(WH.movie_id)
ORDER BY count(WH.movie_id) ASC;

BlockquoteColumn 'Movie.title' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

My assignment is to do the following query:

Show 100 films that have gone so far were looked at. This also means 0 times [film title, number of times viewed]. Make a View for this information requirement.

It gives the error above


@Alexander Volok 2019-03-14 20:22:08

There are plenty of issues:

  • count(WH.movie_id) removed from a GROUP BY
  • Added alias [CountViews]
  • Alias used in ORDER BY instead of aggregate

Fixed SQL:

SELECT TOP(100) M.title, count(WH.movie_id) as [CountViews]
FROM Movie AS M 
inner join WatchHistory AS WH ON M.movie_id = WH.movie_id
GROUP BY M.title
ORDER BY [CountViews] ASC;

@Nițu Alexandru 2019-03-14 20:12:31

You should only group by M.title. If you intend to group rows, decide which columns will be grouped. Remember that only columns in the GROUP BY clause, in addition to aggregate functions such as COUNT, may ultimately be included in the SELECT clause. Grouped aggregate functions operate on sets of rows defined in a GROUP BY clause and return a summarized result. Examples include SUM, MIN, MAX COUNT, and AVG. In the absence of a GROUP BY clause, all rows are considered one set; aggregation is performed on all of them.

Related Questions

Sponsored Content

28 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2440504 View
  • 1658 Score
  • 28 Answer
  • Tags:   sql duplicates

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

25 Answered Questions

[SOLVED] How do I perform an IF...THEN in an SQL SELECT?

27 Answered Questions

43 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

45 Answered Questions

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

11 Answered Questions

[SOLVED] SQL Server: How to Join to first row

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

37 Answered Questions

Sponsored Content