I'm using mysql. I want to count the minimum, maximum, and average number of different tags per movie from one table.

Exclude the duplicate:

• same tag given by the same user to the same movie
• same tag given by different users to the same movie

Example: table 'tags'

Expect result:

I try to write query like below, but it shows an error.

SELECT 
  DISTINCT movieId, 
  MIN(COUNT(DISTINCT tag) AS Min_Tag,
  MAX(COUNT(DISTINCT tag) AS Max_Tag,
  AVG(COUNT(DISTINCT tag) AS Avg_Tag,
FROM (
  SELECT userId,movieId,tag 
  FROM tags
  GROUP BY userId, movieId, tag
) AS non_dup
GROUP BY movieId;

CodePudding user response：

You have to rewrite your query

First you need the count per tag and movie

and fromm that you can calculate min max and avg

SELECT
   movieId,
   MIN(count_tag) AS Min_Tag,
  MAX(count_tag) AS Max_Tag,
  AVG(count_tag) AS Avg_Tag
FROM  
(SELECT movieId,tag, count(*) count_tag
  FROM tags
  GROUP BY movieId, tag) non_dup
GROUP BY movieId

fiddle