Getting confused by MySQL subqueries

Question

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'

Answer 1

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.