List:General Discussion« Previous MessageNext Message »
From:mysql Date:January 23 2006 8:54pm
Subject:Re: count(*) send a wrong value
View as plain text  
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
> 
> 
Thread
count(*) send a wrong valuefabsk23 Jan
  • Re: count(*) send a wrong valuemysql23 Jan
    • Re: count(*) send a wrong valueFabien SK23 Jan
    • Re: count(*) send a wrong valuefabsk23 Jan
    • Re: count(*) send a wrong valueMichael Stassen23 Jan
  • Re: count(*) send a wrong valueRhino23 Jan
    • Re: count(*) send a wrong valuefabsk24 Jan
  • Re: count(*) send a wrong valueHank24 Jan
  • Re: count(*) send a wrong valueMartijn Tonies24 Jan
    • Re: count(*) send a wrong valuefabsk24 Jan
      • Re: count(*) send a wrong valueFabien SK24 Jan
        • Re: count(*) send a wrong valueFabien SK24 Jan