3

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;
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
contactmatt
  • 18,116
  • 40
  • 128
  • 186
  • 1
    Because Oracle has gaps in its support for standard SQL. – Bill Karwin May 25 '10 at 15:25
  • 1
    @Bill: `MySQL` and `SQL Server` don't support this syntax too. – Quassnoi May 25 '10 at 15:29
  • @Quassnoi: MySQL does support the syntax (I just tested it with version 5.1.46). I haven't tested SQL Server. – Bill Karwin May 25 '10 at 15:33
  • @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 May 25 '10 at 15:38
  • @Bill Karwin: `SELECT COUNT(test.*) FROM MY_TABLE test` fails - "Incorrect syntax near '*'" on SQL Server 2005. – OMG Ponies May 25 '10 at 15:39
  • @Quassnoi: Aha, you are correct. I retract my statement. – Bill Karwin May 25 '10 at 16:16
  • Are you trying to get a count of records which have at least one non-null value in a column? In Oracle, to do this you'll have to use something like `SELECT COUNT(*) FROM my_table WHERE COALESCE(colA, colB, colC, colD, etc) IS NOT NULL` - this is not normally useful, of course, since a table with all columns `NULL`able is usually the sign of poor design. – Jeffrey Kemp May 26 '10 at 03:03

4 Answers4

4

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

Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
3

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

Thomas
  • 63,911
  • 12
  • 95
  • 141
  • 3
    For oracle, the 'reason' is that count() requires an expression, whereas [table.]* is not an expression, but a part of syntax offered as part of a SELECT as an alternative to an expression. – Gary Myers May 25 '10 at 23:32
1

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.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 1
    There's a major difference between COUNT(*) and COUNT(column): the former counts all rows; the latter counts the rows where the column value is NOT NULL. They are not interchangeable. – Jonathan Leffler May 25 '10 at 15:43
1

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.

Michael Streeter
  • 11
  • 1