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

Question

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;

Answer 1

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.*.

Answer 2

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.

Answer 3

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.

Answer 4

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.`