Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • by Abigail (26) on 2006.10.02 14:57 (#50676) Journal
    count(expression) counts the number of times expression is not NULL. Which explains your answer. I always use 'count(*)' if I want to count the number of rows - several databases have optimized 'count(*)'. Others use 'count(1)' to count rows.
    • The only two incantations of COUNT() I have used that worked the way I expected were COUNT(*) (to count rows) and COUNT(DISTINCT field) (to count distinct values of a field, and even then I'm not sure what it does with NULL). This is with Oracle; with other RDBMSes YMMV. I'm sure there's some magic I could do with COUNT(field) and COUNT(field1, ..., fieldN), but I don't know how it works. I may have done that in Oracle class or database class at the university, but I didn't retain any information on how

      --
      J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
    • Regrettably, this is something that much documentation [mysql.com] does not make clear. I'm expecting count() to return the number of instances of a particular value. Given that NULL can be thought of as "unknown", I suppose one could argue that it makes sense that it doesn't count the number of values. However, it also seems reasonable for one to assume that count(some_field) will return how many unknown values there are.

      Your explanation is perfectly correct. I'm just frustrated that an arguably "intuitive" answe

      • I'm just frustrated that an arguably "intuitive" answer turns out to be very wrong.

        That's because your "intuitive" answer isn't the "intuitive" answer of one someone who breathes SQL. Just like what you find "intuitive" in Perl isn't "intuitive" for many people who also use Perl.

        I do find the answer "intuitive", but only because I keep myself reminding that a NULL in a relational database is a very special thing, and far more undefined than 'undef' is in Perl. If you try to think of a database 'NULL' as

      • Ditto Abigail about intuition. I think the way COUNT works in SQL w.r.t. NULLs is actually useful and helpful.

        Your stance about NULLs reminds me about the saying about GOTO and the apprentice, the journeyman and the master. (Not that I can claim to be a master, mind…)

      • I guess some of the strange logic is this: if you want to get a count of the number of unique values, you use COUNT(DISTINCT field). If you want to get a count of records, you use COUNT(*). Now what do you want when you ask for COUNT(field)? Do you want a count of distinct values? Use COUNT(DISTINCT field). Do you want a count of records? Use COUNT(*). If you grabbed field and then counted the number of values (not distinct), you'd get absolutely the same results as COUNT(*), right?

        So I guess some

        --
        J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers