SQL Query Syntax : Using table alias in a count is invalid? Why?

2
votes

Could someone please explain to me why the following query is invalid? I'm running this query against an Oracle 10g database.

select count(test.*) from my_table test;

I get the following error: ORA-01747: invalid user.table.column, table.column, or column specification

however, the following two queries are valid.

select count(test.column) from my_table test;

select test.* from my_table test;
4
sqloracleoracle10gora-01747
Because Oracle has gaps in its support for standard SQL. - Bill Karwin
@Bill: MySQL and SQL Server don't support this syntax too. - Quassnoi
@Quassnoi: MySQL does support the syntax (I just tested it with version 5.1.46). I haven't tested SQL Server. - Bill Karwin
@Bill: CREATE TABLE t_count (id INT NOT NULL); SELECT COUNT(t_count.*) FROM t_count;. 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 '*) FROM t_count' at line 1 - Quassnoi
@Bill Karwin: SELECT COUNT(test.*) FROM MY_TABLE test fails - "Incorrect syntax near '*'" on SQL Server 2005. - OMG Ponies

4 Answers

4
votes

COUNT(expression) will count all rows where expression is not null. COUNT(*) is an exception, it returns the number of rows: * is not an alias for my_table.*.

3
votes

As far as I know, Count(Table.*) is not officially supported in the SQL specification. Only Count(*) (count all rows returned) and Count(Table.ColumnName) (count all non-null values in the given column). So, even if the DBMS supported it, I would recommend against using it.`

1
votes

This syntax only works in PostgreSQL and only because it has a record datatype (for which test.* is a meaningful expression).

Just use COUNT(*).

This query:

select count(test.column) from my_table test;

will return you the number of records for which test.column is not NULL.

This query:

select test.* from my_table test;

will just return you all records from my_table.

COUNT as such is probably the only aggregate that makes sense without parameters, and using an expression like COUNT(*) is just a way to call a function without providing any actual parameters to it.

1
votes

You might reasonably want to find the number of records where test.column is not NULL if you are doing an outer join. As every table should have a PK (which is not null) you should be able to count the rows like that if you want:

select count(y.pk)
from x
left outer join y on y.pk = x.ck

COUNT(*) is no good here because the outer join is creating a null row for the table that is deficient in information.