MySQLism: Use SELECT(DISTINCT ) only for one field, use SELECT COUNT(*) FROM (SELECT DISTINCT ... ) ... for multiple

SQL99 specifies COUNT(DISTINCT ) as only taking a single parameter. MySQL and DB/2 support a list of fields for this function, Postgres will support it from version 9.0 and MSSQL and Oracle do not support it in any current versions.

SELECT COUNT(DISTINCT the_field) FROM the_table

is fine on any database engine.

SELECT COUNT(DISTINCT first_field, second_field, third_field) FROM the_table

will only work on MySQL or DB/2 and should rather be written as

SELECT COUNT(*) FROM (SELECT DISTINCT first_field, second_field, third_field FROM the_table)  AS distinct_three

to work more generally.

using DBTNG in Drupal 7 this would look like

db_select($table)
  ->fields($table, array('field1', 'field2'))
  ->distinct()
  ->countQuery();