SELECT MIN(id),
       MAX(id)
FROM  (SELECT film_id,
              COUNT(id) AS id
       FROM   screenings
       GROUP  BY film_id) a; 

CodePudding user response：

It means the dialect of SQL you're using (I think this may even be standard) requires that every subquery in the FROM by given an alias, even if that alias is not required anywhere else in the query. Of course, you could use the alias now it's defined:

SELECT MIN(a.id),MAX(a.id)
FROM
    (SELECT film_id, COUNT(id) AS id FROM screenings GROUP BY film_id) a;

But it's not usually necessary if it's clear where each column is coming from (as it is here with only one data source in the FROM).

CodePudding user response：

The a that comes in the last of your query is an alias it is a temporary name and can be use for column and table also. In your query, you are trying to fetch min id and max id from sub query.

For more research about alias click here

CodePudding user response：

The terminology for the query in your example is a derived table.

The select..from screenings is producing what is known as a derived table, and your outer select is using it in the context of consuming its resultset as if it were a table in its own right - and the standard SQL syntax requires it is given an alias by which to identify it, even though in this case it's the only "table".

CodePudding user response：

One thing to be careful of is that the naming in the subselect is confusing. You have COUNT(id) AS id, but a better name for that count would be something like num_screenings, because it's counting the number of screenings that the given film_id appeared at.

Subselects can often be clearly written as a CTE (common table expression). This makes them easier to understand because they are in small self-contained segments.

So, if we combine these three changes:

• Changing the subselect to a CTE
• Use the descriptive name screening_counts instead of a
• Change the COUNT(id) AS id to AS num_screenings

we get this that is much clearer:

WITH screening_counts AS (
    SELECT film_id, COUNT(id) AS num_screenings
    FROM screenings
    GROUP BY film_id
)
SELECT
MIN(num_screenings) AS min_screenings,
MAX(num_screenings) AS max_screenings
FROM screening_counts;