Slash Boxes
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.
More | Login | Reply
Loading... please wait.
  • 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.
    • by Ovid (2709) on 2006.10.02 16:43 (#50678) Homepage Journal

      Regrettably, this is something that much documentation [] 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" answer turns out to be very wrong. I should have remembered that I absolutely must avoid NULLs whenever possible, but in this case, I threw together a quick query to examine a database problem and was mystified that my results were clearly contrary to my expectations.

      I'll remember to use count(*) or count(1) in the future, when possible.

      • 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