CODEWITHSUNDEEP
sqlansi-sql
c12
c12

Reputation: 9827

ANSI Support of Select Count SQLStatements

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

Upvotes: 1

Views: 2397

Answers (2)

onedaywhen
onedaywhen

Reputation: 57023

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!

Upvotes: 0

Dawood ibn Kareem
Dawood ibn Kareem

Reputation: 79838

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.

Upvotes: 2

Related Questions