-1

i have syntax

select count(*) as total 
from ( select a.users_id 
       from order_star_member a 
       where a.createdAt >= '2019-12-01' and a.createdAt < '2020-01-01' ) 
group by users_id 
having sum(total_price_star_member) >= 600000;

and the error was like this:

Error Code: 1248. Every derived table must have its own alias

do you guys know why?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
18Man
  • 572
  • 5
  • 17
  • Does this answer your question? [What is the error "Every derived table must have its own alias" in MySQL?](https://stackoverflow.com/questions/1888779/what-is-the-error-every-derived-table-must-have-its-own-alias-in-mysql) – FanoFN Jan 22 '20 at 05:08

2 Answers2

1

It is enough to add alias, i.e. new name to the sub-query in round brackets:

select b.users_id, count(*) as total
from (
  select a.users_id, a.total_price_star_member
  from order_star_member a 
  where a.createdAt >= '2019-12-01' and a.createdAt < '2020-01-01'
) b
group by b.users_id 
having sum(b.total_price_star_member) >= 600000

You already have the alias a, so let's name the new entity b.

artoodetoo
  • 918
  • 10
  • 55
  • Do you see that your query will fail? – Akina Jan 22 '20 at 05:41
  • @Akina No, I don't have a sanbox to test it. At first look the query is okay. If you have the sandbox, please provide a link so that I can check and fix. Thank you in advance. – artoodetoo Jan 22 '20 at 06:16
  • HAVING needs `b.total_price_star_member` field - where it will be taken from? no such field in a subquery. – Akina Jan 22 '20 at 06:31
  • You are right, thanks. I will update my variant despite that OP has already chosen the answer :) – artoodetoo Jan 22 '20 at 06:46
1

  1. You must add an alias to your subquery. Each recordset (source, intermediate) must have an alias (unique in its scope of visibility) for to refer to, now the subquery have no any alias, and no way to refer to it and/or its fields.

  2. In subquery you must select all fields which are used in outer query - if not then the error "unknown field" will be raised.

So the final state of your query may be

SELECT COUNT(*) AS total 
FROM ( SELECT a.users_id, 
              a.total_price_star_member 
       FROM order_star_member a 
       WHERE a.createdAt >= '2019-12-01' 
         AND a.createdAt < '2020-01-01' ) some_alias
GROUP BY some_alias.users_id 
HAVING SUM(some_alias.total_price_star_member) >= 600000;

PS. If your logic is "count the users which's sum of total_price_star_member in specified period is not less than 600000" then your query logic is wrong. The COUNT(*) in the query will not count the amount of such users, it will count the payments count for each user with such total payments value.

Akina
  • 39,301
  • 5
  • 14
  • 25