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 NULL
s 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
?
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With