ANSI Support of Select Count SQLStatements

Question

I'm wondering if there is a list of supported Select Count SQL statements per the ANSI standard? The below three variations are what I know of. Can the where clause be used on all three below?

SELECT COUNT(*) AS RowCount FROM table_name
SELECT COUNT(ColumnName) AS RowCount FROM table_name
SELECT COUNT(DISTINCT ColumnName) AS RowCount FROM table_name

Seawards

There is also the OVER clause variants. What version of the ANSI standard are you referring to? – Thera

@MartinSmith - I'm writing a JDBC driver and was wondering what is commonly supported. I wasn't sure if there was an ANSI SQL standard or some other standard that listed what is usually supported.... – Seawards

Answer 1

The SQL standard that almost all DBMS's use is the ANSI 92 standard, which can be found at http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt. Page 124 has the information that you are looking for. Most DBMSs offer something in addition to the ANSI 92 standard, but this is kind of the lowest common denominator of all of them.

Tranquil

Answer 2

The Standard spec give special meaning to COUNT(*). Otherwise, ColumnName is any valid expression that is implementation defined.

BTW you missed one:

SELECT COUNT(ALL ColumnName) AS RowCount FROM table_name;

As with SELECT ALL, the ALL is the default and can be omitted -- and almost always is!

Cantaloupe