SELECT文でのエラー

Question

以下のような３つのテーブルがあります。
Movies

Users

Ratings

create文です、データはcsvファイルからとってきたものです。

CREATE DATABASE IF NOT EXISTS MovieDB;
USE MovieDB;
DROP TABLE IF EXISTS Ratings;
DROP TABLE IF EXISTS Users;
DROP TABLE IF EXISTS Movies;
CREATE TABLE Users (
  UserID INTEGER PRIMARY KEY,
  Age INTEGER,
  Gender CHAR(1),
  Occupation VARCHAR(20),
  ZipCode CHAR(5));
CREATE TABLE Movies (
  MovieID INTEGER PRIMARY KEY,
  Title VARCHAR(120),
  ReleaseDate DATE,
  UNKNOWN ENUM('T','F'),
  Action ENUM('T','F'),
  Adventure ENUM('T','F'),
  Animation ENUM('T','F'),
  Children ENUM('T','F'),
  Comedy ENUM('T','F'),
  Crime ENUM('T','F'),
  Documentary ENUM('T','F'),
  Drama ENUM('T','F'),
  Fantasy ENUM('T','F'),
  FilmNoir ENUM('T','F'),
  Horror ENUM('T','F'),
  Musical ENUM('T','F'),
  Mystery ENUM('T','F'),
  Romance ENUM('T','F'),
  SciFi ENUM('T','F'),
  Thriller ENUM('T','F'),
  War ENUM('T','F'),
  Western ENUM('T','F'));
CREATE TABLE Ratings (
  UserID INTEGER,
  MovieID INTEGER,
  Rating INTEGER,
  TS TIMESTAMP,
  PRIMARY KEY (UserID, MovieID),
  FOREIGN KEY (UserID) REFERENCES Users(UserID),
  FOREIGN KEY (MovieID) REFERENCES Movies(MovieID)
);

次の条件のセレクト文を考えているのですが行き詰っています。
1つ目
50 歳以下のユーザーによる評価が1件もない映画のタイトル。

　映画ごとに50歳以下のユーザーの評価をカウントし、それが0かどうかで判断するというように考えましたが、エラーがでます。

 select title from movies,users,ratings where count(movies.movieid = ratings.movieid and ratings.userid = users.userid and age < 50) = 0;

ERROR 1111 (HY000): Invalid use of group function

2つ目
400人以上のユーザーから評価をつけられている映画のタイトルと平均評価を平均評価の高い順に表示。

UserIDをカウントして400以上であるかどうかで考えましたが、エラーが出ます。

 select title avg(ratings) as avg_r from movies,users,ratings where movies.movieid = ratings.movieid and ratings.userid = users.userid and count(users.userid) >= 400 order by avg_r desc;

 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(ratings) as avg_r from movies,users,ratings where movies.movieid = ratings.movi' at line 1

Comments

[yambejp]

画像ではなくSQLのサンプルはcreate table/insert形式で例示ください

[Beginnerman]

データの中身はcsvファイルからとってきたものです。
CREATE DATABASE IF NOT EXISTS MovieDB;
USE MovieDB;
DROP TABLE IF EXISTS Ratings;
DROP TABLE IF EXISTS Users;
DROP TABLE IF EXISTS Movies;
CREATE TABLE Users (
UserID INTEGER PRIMARY KEY,
Age INTEGER,
Gender CHAR(1),
Occupation VARCHAR(20),
ZipCode CHAR(5));
CREATE TABLE Movies (
MovieID INTEGER PRIMARY KEY,
Title VARCHAR(120),
ReleaseDate DATE,
UNKNOWN ENUM('T','F'),
Action ENUM('T','F'),
Adventure ENUM('T','F'),
Animation ENUM('T','F'),
Children ENUM('T','F'),
Comedy ENUM('T','F'),
Crime ENUM('T','F'),
Documentary ENUM('T','F'),
Drama ENUM('T','F'),
Fantasy ENUM('T','F'),
FilmNoir ENUM('T','F'),
Horror ENUM('T','F'),
Musical ENUM('T','F'),
Mystery ENUM('T','F'),
Romance ENUM('T','F'),
SciFi ENUM('T','F'),
Thriller ENUM('T','F'),
War ENUM('T','F'),
Western ENUM('T','F'));
CREATE TABLE Ratings (
UserID INTEGER,
MovieID INTEGER,
Rating INTEGER,
TS TIMESTAMP,
PRIMARY KEY (UserID, MovieID),
FOREIGN KEY (UserID) REFERENCES Users(UserID),
FOREIGN KEY (MovieID) REFERENCES Movies(MovieID)
);

[yambejp]

ソースはマークダウンして本文に追記してください

Answer 1

select ... avg(カラム名) ...

or

select ... avg(テーブル名.カラム名) ...

Answer 2

select title from movies,users,ratings where count(movies.movieid = ratings.movieid and ratings.userid = users.userid and age > 50) = 0;

なにをカウントしているかさっぱりわかりませんが

select title from movies,users,ratings 
where 1
and movies.movieid = ratings.movieid
and ratings.userid = users.userid
and users.age < 50;

的なことではないのでしょうか？
※質問者さんの意向にあわせて不等号を逆にしました

検証1

ざっくりデータを削ります

create table movies(mid int primary key,title varchar(100));
insert into movies values(1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd'),(5,'eee'),(6,'fff');

create table users(uid int primary key,name varchar(10),age int);
insert into users values(1,'AAA',30),(2,'BBB',40),(3,'CCC',55),(4,'DDD',60);

create table ratings(rid int primary key,uid int,mid int,rating int);
insert into ratings values(1,1,1,10),(2,1,2,20),(3,2,1,15),(4,2,2,20),(5,2,3,12),(6,4,2,30),(7,5,4,20);

select * from movies as t1
left join ratings as t2 on  t1.mid = t2.mid
and ratings.uid = users.uid
and users.age < 50;

上記50以下のユーザーuid=1,2が評価したタイトルはmid=1,2,3
評価していないのはmid=4,5,6

select * from movies as t1 where 
not exists(
select 1 from ratings as t2
inner join users as t3 on t2.uid = t3.uid
and t3.age < 50
where mid=t1.mid)

※解説
where not existsは相関サブクエリといいます。
moviesのmidを利用して、サブクエリ内50以下の評価があるmidを
not existsで反転して評価がないものを抽出しています

検証2

サンプル数が少ないのでとりあえず2件以上評価があるもの
mid=1,2とその平均評価

select t1.mid,t1.title,t2.avg_rating
from movies as t1
inner join (
select mid,avg(rating) as avg_rating
from ratings
group by mid
having count(*)>=2
) as t2 on t1.mid=t2.mid
order by avg_rating desc

※解説
単純にサブクエリないでmidごとの平均ratingを計算し
n件以上をhavingで抽出しています
group by~having構文は最低限覚える必要があるSQLの一つです
あとはサブクエリとしてmovieに結合しています

Comments

[Beginnerman]

50歳以下の評価が0のtitleを出力したいので、50歳以下の評価をカウントし、それが0のものをと思いました。
```
avg > 50
```
の部分は不等号が反対でした。

[yambejp]

＞ 50歳以下の評価が0のtitle

評価が0とは、50歳以下の人がまったく評価をあげていないタイトルのこと？
それとも何かの評価の点が0点ということ？

[Beginnerman]

評価をしていないタイトルです。評価点ではないです。

[yambejp]

サンプル追記しときました

[Beginnerman]

ありがとうございます。軽い解説を頂ければ幸いです。初心者なのでここまで色んな句を使ったセレクト文を見たことが無かったので。

[yambejp]

解説つけておきました
本ちゃんのサンプルがどう動くか、スピードが確保できるかなどは
こちらではなんとも言えないのでチューニングが必要な場合は
インデックスを工夫して、SQL文ももっときちんと組まないといけないかもしれません