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 editor@searchDatabase.com 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.