Last updated February 16, 2010. Created by bellHead on February 6, 2010.
Log in to edit this page.
SQL99 specifies COUNT(DISTINCT ) as only taking a single parameter. MySQL and DB/2 support a list of fields for this fuction, 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_tableis fine on any database engine.
SELECT COUNT(DISTINCT first_field, second_field, third_field) FROM the_tablewill 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_threeto work more generally.
using DBTNG in Drupal 7 this would look like
db_select($table)
->fields($table, array('field1', 'field2'))
->distinct()
->countQuery();