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

2 comments

@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

33 Answered Questions

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

28 Answered Questions

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

24 Answered Questions

25 Answered Questions

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

25 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2291592 View
  • 1551 Score
  • 25 Answer
  • Tags:   sql duplicates

11 Answered Questions

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

44 Answered Questions

4 Answered Questions

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

38 Answered Questions

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

37 Answered Questions

Sponsored Content