SQL Error (1248): Every derived table must have its own alias

Databases
1

I have problem with this query; I keep getting the following error:

SQL Error (1248): Every derived table must have its own alias

SELECT ,
SUM(actions_in_group) AS total_rows_in_group,
GROUP_CONCAT(in_collection) AS complete_collection
FROM
(SELECT stream.,
COUNT(stream.id) AS actions_in_user_group,
GROUP_CONCAT(stream.id) AS actions_in_user_collection
FROM stream
INNER JOIN follows
ON stream.user_id = follows.following_user
WHERE follows.user_id = ‘0’
GROUP BY stream.user_id,
date(stream.stream_date)
)
GROUP BY object_id,
date(stream.stream_date)
ORDER BY stream.stream_date DESC;

2 
FROM ( subquery )

that’s a derived table, and it needs an alias, like this –

FROM ( subquery ) AS ishmael

i.e. you can call it anything :wink:

p.s. you’re gonna get a “something.something is undefined” error next… go ahead and post your revised query when you do

1 Like
4

See: http://sqlfiddle.com/#!9/4ddf0/10

6

I get the following: Unknown column ‘actions_in_group’ in ‘field list’,

SELECT ,
SUM(actions_in_group) AS total_rows_in_group,
GROUP_CONCAT(in_collection) AS complete_collection
FROM (SELECT stream.,
COUNT(stream.id) AS actions_in_user_group,
GROUP_CONCAT(stream.id) AS actions_in_user_collection
FROM stream
INNER JOIN follows
ON stream.user_id = follows.following_user
WHERE follows.user_id = ‘0’
GROUP BY stream.user_id,
date(stream.stream_date)
) As something
GROUP BY object_id,
date(stream.stream_date)
ORDER BY stream.stream_date DESC;

8

very simple to understand, once you figure it out

your query has this general form –

SELECT * , SUM(actions_in_group) AS total_rows_in_group , GROUP_CONCAT(in_collection) AS complete_collection FROM ( SELECT ... ) AS something GROUP BY object_id , date(stream.stream_date) ORDER BY stream.stream_date DESC
as you can see, the main query contains only a (derived) table called something

any table names used inside the something subquery are not available to the outer main query

so whatever you want the outer query to reference in the GROUP BY and ORDER BY clauses, you have to make sure to qualify those column names with something

also, make sure that every column you want to reference in the outer query is actually included in the SELECT clause of the subquery

1 Like
9

I start to see something, but I cannot put my finger on it.

10

SELECT ,
SUM(actions_in_user_group) AS total_rows_in_group,
GROUP_CONCAT(actions_in_user_collection) AS complete_collection
FROM (SELECT stream.,date(stream.stream_date) groupby_stream_date,
COUNT(stream.id) AS actions_in_user_group,
GROUP_CONCAT(stream.id) AS actions_in_user_collection
FROM stream
INNER JOIN follows
ON stream.user_id = follows.following_user
WHERE follows.user_id = ‘0’
GROUP BY stream.user_id,
date(stream.stream_date)
) As something
GROUP BY object_id,
groupby_stream_date
ORDER BY groupby_stream_date DESC;

This worked.

1 Like
11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.