Bug #11962 Unable to use * with multiple count
Submitted: 15 Jul 2005 10:03 Modified: 1 Aug 2005 8:05
Reporter: [ name withheld ] (Basic Quality Contributor) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.7 OS:Any (*)
Assigned to: Sergei Golubchik CPU Architecture:Any

[15 Jul 2005 10:03] [ name withheld ]
Description:
Server does not allow to use count(*) or count(table.*) in a query with multiple count, however if you use count(table.field) it works fine.

How to repeat:
Run a query like this on two tables:

SELECT
count(c.*), (
SELECT count(p.*) FROM prodotti p
) FROM clienti c

You'll get:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*), (
SELECT count(p.*) FROM prodotti p
) FROM clienti c' at line 2

This one runs fine:
SELECT
count(c.idcliente), (
SELECT count(p.idprodotto) FROM prodotti p
) FROM clienti c
[15 Jul 2005 11:29] Aleksey Kishkin
test case

Attachment: bug11962.sql (text/x-sql), 174 bytes.

[15 Jul 2005 11:29] Aleksey Kishkin
added testcase
[1 Aug 2005 8:05] Sergei Golubchik
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

According to SQL:2003, part 2 "Foundations", the syntax for the "aggregate function" is

<aggregate function> ::=
    COUNT <left paren> <asterisk> <right paren> [ <filter clause> ]
  | <general set function> [ <filter clause> ]
  | <binary set function> [ <filter clause> ]
  | <ordered set function> [ <filter clause> ]

<general set function> ::=
    <set function type> <left paren> [ <set quantifier> ]
    <value expression> <right paren>

<set function type> ::= <computational operation>

<computational operation> ::=
    AVG
  | MAX
...
  | COUNT
...

As you can see, in parens after the COUNT one can use either a <value expression> or an <asteriks>. COUNT(c.*) is a syntax error.