List:General Discussion« Previous MessageNext Message »
From:Fabien SK Date:January 23 2006 10:30pm
Subject:Re: count(*) send a wrong value
View as plain text  
Thank you for you answer, but I read many times and I did not found
something to answer my question (well, I did not know about the NULL).

In my case:
- there is one table
- htere is no "distinct"
- there is a WHERE clause, so there is no optimisation
- there is no other field and no "group by"

If I do "count(cid)", I still get "2".

Fabien

Le lundi 23 janvier 2006 à 20:54 +0000, mysql@stripped a écrit : 
> >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