every derived table must have its own alias mysql database

Question

I am running this query on MySQL and it is giving this error:

Every derived table must have its own alias.

SELECT MAX (mycount) FROM 
(SELECT CreatedBy,COUNT(CreatedBy) mycount 
FROM audit_csp_evaluation 
GROUP BY CreatedBy);

Incidentally, it's a little odd to GROUP BY the thing you're counting. — Strawberry, Sep 25 '19 at 15:26
@Strawberry, yes, it's odd but has no practical disadvantage, except that the count will be 0 if you have a group where CreatedBy is NULL. — Bill Karwin, Sep 25 '19 at 15:40

kjmerf · Answer 1 · 2019-09-25T18:04:38.210

1

You have to name the derived table, like this:

SELECT MAX(x.mycount) AS max_mycount
FROM 
(
 SELECT CreatedBy
    , COUNT(CreatedBy) AS mycount 
FROM audit_csp_evaluation 
GROUP BY CreatedBy
) x;

x is the name I'm giving the derived table.

edited Sep 25 '19 at 18:04

answered Sep 25 '19 at 15:20

kjmerf

Thanks @kjmerf. Please remove Space after MAX. It gives error with space. – Manoranjan Jena Sep 25 '19 at 17:54
@ManoranjanJena done thanks – kjmerf Sep 25 '19 at 18:04

score 0 · Accepted Answer · answered Sep 25 '19 at 15:20

0

You need to give a name to the table derived from your subquery.

SELECT MAX(c.mycount) FROM 
(
 SELECT CreatedBy,COUNT(CreatedBy) mycount 
 FROM audit_csp_evaluation 
 GROUP BY CreatedBy
 )c <---added name here;

answered Sep 25 '19 at 15:20

isaace

2 Answers2