From the MySQL 4.1 manual
12.10.1. GROUP BY (Aggregate) Functions
COUNT(expr)
Returns a count of the number of non-NULL values in the rows
retrieved by a SELECT statement.
COUNT() returns 0 if there were no matching rows.
mysql> SELECT student.student_name,COUNT(*)
-> FROM student,course
-> WHERE student.student_id=course.student_id
-> GROUP BY student_name;
COUNT(*) is somewhat different in that it returns a count
of the number of rows retrieved, whether or not they contain
NULL values.
COUNT(*) is optimized to return very quickly if the SELECT
retrieves from one table, no other columns are retrieved,
and there is no WHERE clause. For example:
mysql> SELECT COUNT(*) FROM student;
This optimization applies only to MyISAM and ISAM tables
only, because an exact record count is stored for these
table types and can be accessed very quickly. For
transactional storage engines (InnoDB, BDB), storing an
exact row count is more problematic because multiple
transactions may be occurring, each of which may affect the
count.
COUNT(DISTINCT expr,[expr...])
Returns a count of the number of different non-NULL values.
COUNT(DISTINCT) returns 0 if there were no matching rows.
mysql> SELECT COUNT(DISTINCT results) FROM student;
In MySQL, you can get the number of distinct expression
combinations that do not contain NULL by giving a list of
expressions. In standard SQL, you would have to do a
concatenation of all expressions inside COUNT(DISTINCT ...).
COUNT(DISTINCT ...) was added in MySQL 3.23.2.
Keith
In theory, theory and practice are the same;
In practice they are not.
On Mon, 23 Jan 2006, fabsk wrote:
> To: mysql@stripped
> From: fabsk <fabsk+mysql@stripped>
> Subject: count(*) send a wrong value
>
> Hi,
>
> I'm facing a strange problem. I am using a database at my Internet
> provider (Free, France). The type of table is MyISAM (no choice), MySQL
> 4.1.15. I can do my tests with my PHP code or phpMyAdmin.
>
> The definition of my table is:
> - uid, int
> - cid, int
> - response, text
> - points, int (can be null)
>
> keys:
> - uid, cid
> - cid, response(4)
> - cid
>
> When I do "select * from my_table where cid=123", I get my 10 records.
> But when I do "select count(*) from my_table where cid=123" I get "2". I
> also happens with many other values of "cid" and the bad result is
> always "2".
>
> I can't understand what's happen. It seems to simple, but there should
> be something. Do you have an idea?
>
> Thank you for your attention
> Fabien
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
>
