Why doesn't something like select count((select * from producers)) from producers; work?

I have defined the following query:

select count((select * from producers)) from producers;

Assuming a producers table with 3 columns (A, B and C) and 2 rows:

A B C
-----
0 1 2
3 4 5

I'd expect the following output:

2
2

It doesn't work. While the query itself is basically useless (even if it worked, it wouldn't yield any useful output), I'd like to try to understand why this doesn't run.

(select * from producers)

This would yield a list of rows with information on all the attributes on the producers table.

select count((select * from producers)) from producers;

This one will for each row on produ开发者_如何学JAVAcers, show up the number 2 (the number of elements in producers).

Why doesn't it work? SQL limitation? Is there anything wrong with the logic I'm following here?

Thanks

It is a limitation of SQL, as far as I know. Subqueries are not allowed in the COUNT expression. Obviously (select * from producers) is a subquery, so it's not allowed there.

I think your misunderstanding is that you're thinking that you would call the function like COUNT(SELECT * FROM producers) whereas in SQL it's like SELECT COUNT(*) FROM producers.

Functions like MAX, MIN, SUM, and COUNT are aggregate functions, meaning that they take a scalar argument but execute once for each row, accumulating results every iteration. So SELECT MAX(column) FROM table executes the MAX function once for each row in table, while you might be thinking that MAX executes once and gets passed in every row in table.

Contrast this with operators like IN, EXISTS, ANY, and ALL, which have a subquery as an argument. They are effectively passed all the results of their subquery every time they are invoked.

It should just be

Select count(1) from producers;

If you are asking about inner selects, then the inner select must be part of the from clause, e.g.

Select count(1) from (select * from producers)

Both of these do the same but the first is more efficient.

COUNT() function expects only one value.

This will return what you want:

    SELECT COUNT(*)
      FROM producers p1
CROSS JOIN producers p2
  GROUP BY p1.A

"Why does Count() only expect a value? Doesn't Count() take as input a set of rows? "

Count accepts an expression. If the expression evaluates to null, then it isn't counted. Otherwise it is.

If a table has five rows in it, and one column has three actual values and two null values, then a count of that column would return three.

ID  Colour  Size
1   Red     30
2   Blue    <null>
3   <null>  20
4   <null>  <null>
5   Blue    10

SELECT COUNT(COLOUR), COUNT(SIZE) would give 3 and 3.

COUNT(*) is special in that it gives the number of rows in the table, irrespective of any nulls.

COUNT can't/won't work with (select * from producers) or even (1,3) as they are not expressions that can be interpreted as null or not null.