CODEWITHSUNDEEP
sqloracle-databaseoracle10gora-01747
contactmatt
contactmatt

Reputation: 18610

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

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;

Upvotes: 3

Views: 8522

Answers (4)

Michael Streeter
Michael Streeter

Reputation: 11

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.

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425533

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.

Upvotes: 1

Thomas
Thomas

Reputation: 64655

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

Upvotes: 3

Vincent Malgrat
Vincent Malgrat

Reputation: 67742

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

Upvotes: 4

Related Questions