Getting confused by MySQL subqueries
I'm trying to learn how to do subqueries, and I'm really confused at what's wrong with this simple example.
开发者_开发技巧My first try was
SELECT COUNT(SELECT * FROM my_table);
but that didn't work (I guess because I need a temporary table?) so I tried this:
SELECT COUNT(items)
FROM (SELECT * FROM my_table) AS items;
Why do I get the following:
1054: Unknown column 'items' in 'field list'
You're getting the error because in this example items
is a table (as it is an alias), not a column. Simplest solution is to use:
SELECT COUNT(*)
FROM (SELECT * FROM my_table) AS items
Aggregate functions (IE: COUNT, MIN, MAX, AVG, etc) only work on column references, but some accept [table].* as a parameter.
精彩评论