Are COUNT and DISTINCT SQL-92 compliant?

I need to know whether the SQL functions COUNT and DISTINCT are SQL-92 compliant or not. That is, if I'm using an SQL-92 compliant database, may I expect them to be supported?

Yes, but watch out for the nuances.

COUNT(*) is the number of rows, without regard for the contents of any column.

COUNT([ALL] expression) is the number of non-null values of the expression -- usually a column name. ALL is the default, so it's not necessary. You see it used typically like COUNT(foobar) where foobar is a column -- the result is the number of non-null values.

COUNT(DISTINCT expression) is the number if distinct non-null values.

For More Information

• What do you think about this answer? E-mail the edtiors at [email protected] with your feedback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.