select max() from count()

Question

Possible Duplicate:
every derived table must have its own alias

I need to find maximum of actions per user

table columns: action_id, action_status, user

request:

SELECT MAX(`counted`) FROM
(
SELECT COUNT(*) AS `counted`
FROM `table_actions`
WHERE `status` = "good"
GROUP BY `user`
)

error message: "Every derived table must have its own alias"

what is wrong?..

Next time please search Stack Overflow for an already existing answer. — kapa, Apr 28 '12 at 12:36

score 26 · Accepted Answer · edited Jan 04 '15 at 00:10

26

That just means MySQL insists that you give the inner SELECT a name, like:

SELECT MAX(counted) FROM
(
    SELECT COUNT(*) AS counted
    FROM table_actions
    WHERE status = "good"
    GROUP BY user
) AS counts;

edited Jan 04 '15 at 00:10

potashin

answered Apr 28 '12 at 12:35

Michael Slade

omg... such small change... thx! – Zdomb Apr 28 '12 at 12:39
2

if you also select the row status, it won't be accurate: `select max(counted), status from (select count(*) as counted, status ...` won't work... the field "status" won't be accurate. I don't really know how to fix this – pr00thmatic Nov 30 '15 at 01:34

1 Answers1