Explaining COUNT return value when used without group

user_1080354 0
Viewed 339

COUNT returns the number of items found in a group and by default it includes NULL values and duplicates.

The first two statements returns an error and the second two - NULL:

1/0
1/'A'
1/NULL
NULL/1

But wrapped in COUNT they returns 1 and 0:

SELECT COUNT(1/0);    -- 1
SELECT COUNT(1/'A');  -- 1
SELECT COUNT(1/NULL); -- 0
SELECT COUNT(NULL/1); -- 0

The NULL cases can be explain by the docs as

COUNT(ALL expression) evaluates expression for each row in a group, and returns the number of nonnull values.

So, the are evaluated, they returns NULLs and as non null values are only count, we get 0.

I am wondering why the first two returns 1?

Having the values in a table, everything seems normal - error is thrown and no value is returned:

DECLARE @DataSource TABLE
(
    [valueA] INT
   ,[valueB] INT
);

INSERT INTO @DataSource ([valueA],[valueB])
VALUES (1, 0);

SELECT COUNT([valueA]/[valueB])
FROM @DataSource;

(1 row affected) Msg 8134, Level 16, State 1, Line 16 Divide by zero error encountered.

Completion time: 2020-03-22T13:47:44.5512536+02:00

Maybe this 1 row affected is returned as COUNT?

count group-by tsql sql-server sql
2 Answers

When constants are specified in the COUNT expression, SQL Server can optimize the query at compile time an avoid evaluating the expression at execution time. The resultant value can never be NULL in the first 2 queries and will always be NULL in the last 2 queries.

Below is a snippets from the SELECT COUNT(1/0) and SELECT COUNT(1/NULL) execution plans:

<ScalarOperator ScalarString="Count(*)">
<Const ConstValue="NULL" />

The "1 row affected" message generated by the client tool (SSMS), reflecting the rowcount returned by the INSERT statement. If undesired, add SET NOCOUNT ON; to the beginning of the script.

user_37111620

As you can see in the docs that you copied further, COUNT(<expression>) counts each and every non-null value. If you want to count all records in the group, regardless of the value of this or that column, you need COUNT(*) (or other equivalent expressions, such as COUNT(1)).

The expression within the COUNT() is evaluated for each row; if an error occurs, then the whole query errors. I would tend to consider SELECT COUNT(1/0) returning 1 a counter-intuitive behavior, possibly due to premature optimization (SELECT 1/0 does error).

But in real-life situation, as in your script, you can see that an error is raised:

Divide by zero error encountered.

Edit - testing this on other databases shows no consistency in the results:

  • In MySQL, MariaDB 10.4, and SQLite: SELECT COUNT(1/0) returns 0

  • In Postgres: ERROR: division by zero

  • In Oracle: ORA-01476: divisor is equal to zero

  • In DB2: returns 1

user_106767160
Related