Using subquery in SQL that is returning Every derived table must have its own alias error?

user_14262041 0
Viewed 58

I'm finding it difficult to grasp why this following query wouldn't work:

SELECT rating_count 
FROM 
    (SELECT 
         title, 
         COUNT(rating) AS rating_count
     FROM series
     LEFT JOIN reviews ON series.id = reviews.series_id
     GROUP BY series.id);

The result of the subquery is the following:

enter image description here

Why is it that when I try to query for the rating_count, it wouldn't work? I thought I could request this. My intention is filter those rows that have rating_count = 0.

I wanted to try:

SELECT * 
FROM 
    (SELECT
         title, COUNT(rating) AS rating_count
     FROM series
     LEFT JOIN reviews ON series.id = reviews.series_id
     GROUP BY series.id) 
WHERE rating_count = 0;
sql
1 Answers

Exactly as the error says, you need to alias your derived table:

SELECT * FROM (
    SELECT
    title, 
    count(rating) as rating_count
    FROM series
    LEFT JOIN reviews
        ON series.id = reviews.series_id
    GROUP BY series.id
    ) AS t
WHERE rating_count=0;
user_176450990
Related