stipe42 wrote:
I believe the difference is that count(*) includes nulls (because it is counting the number of records), whereas count(column) only counts the records where the column being counted is not null, regardless of the total number of rows.
Right. COUNT(*) counts rows, COUNT(col) counts non-null values in col.
Hmm, on a related question then if I am correct above, does count(distinct column) count NULL as a distinct value or not? I.e. if I've got four records in a table with one column: (null, a, a, b), will count(distinct column) return 3 or 2?
COUNT() doesn't count NULLS. "SELECT DISTINCT col FROM yourtable" will return NULL, 'a', and 'b', but "SELECT COUNT(DISTINCT col) FROM yourtable" will return 2, because there are 2 non-NULL values to count. This is easily verified:
mysql> SELECT * FROM news; +----+---------+ | id | subject | +----+---------+ | 1 | cars | | 2 | toys | | 3 | books | | 4 | NULL | | 5 | toys | +----+---------+ 5 rows in set (0.00 sec) mysql> SELECT COUNT(DISTINCT subject) FROM news; +-------------------------+ | COUNT(DISTINCT subject) | +-------------------------+ | 3 | +-------------------------+ 1 row in set (0.00 sec) mysql> SELECT subject, COUNT(*), COUNT(subject) FROM news GROUP BY subject; +---------+----------+----------------+ | subject | COUNT(*) | COUNT(subject) | +---------+----------+----------------+ | NULL | 1 | 0 | | books | 1 | 1 | | cars | 1 | 1 | | toys | 2 | 2 | +---------+----------+----------------+ 4 rows in set (0.00 sec)
stipe42
Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
