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

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:

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 that 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;

CodePudding user response：

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;