Home Forums Article Discussions Article Discussions by Author Discuss content posted by Prakriti Agrawal COUNT_BIG RE: COUNT_BIG

  • March 9, 2010 at 7:35 am

    #1130651

    sjimmo (3/9/2010)

    Good question - Thought that I knew the answer, and was wrong. Now, I have a question. I ran the following against an existing database to prove/disprove the answer, and found the following:

    SELECT COUNT_BIG(*) FROM test_table

    98481

    SELECT COUNT_BIG(testfield2) FROM test_table

    98481

    SELECT COUNT_BIG(ALL testfield2) FROM test_table

    98481

    SELECT COUNT_BIG(DISTINCT testfield2) FROM test_table

    28452

    Based upon testing, though brief, and the answer, I would assume that the correct answer is 1, 2, and 3. The answer description states that ALL is the default, whether used or not thus selection 1 would use the default of ALL as shown in the above example, correct?

    No. It appears that you have no NULL values in testfield2. Here's the breakdown:

    (a) COUNT_BIG(*) is essentially a row count function -- it returns the number of rows returned, including those with NULL values in any column.

    (b) COUNT_BIG(testfield2) will return the number of rows where testfield2 is NOT NULL.

    (c) COUNT_BIG(ALL testfield2) is the same as the previous, as ALL is the default.

    (d) COUNT_BIG(DISTINCT testfield2) will return the number of unique nonnull values of testfield2

    So, (b) and (c) are always equal. If (a) is equal to (b) and (c), that means you have no NULLs in testfield2 in your result set. If (d) is equal to (b) and (c), that means you have no duplicate values in testfield2 in your result set. If they're all 4 equal, that means testfield2 is a column with all unique non-null values.