Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


Friends, I have a quick question for you. Why does SUM of an empty table returns NULL & Not 0 ?  Let me just create some sample examples for you.

Example 1

--

CREATE TABLE testSUMNulls
(
	ID TINYINT
)
GO

INSERT INTO testSUMNulls (ID) VALUES (NULL),(NULL),(NULL),(NULL)

SELECT SUM(ID) FROM testSUMNulls

--

Example 2

--

CREATE TABLE testSumEmptyTable
(
	ID TINYINT
)
GO

SELECT SUM(ID) Sums FROM testSumEmptyTable

--

Example 3

--

CREATE TABLE testSumTable
(
	ID TINYINT
)
GO

INSERT INTO testSumTable(ID)
VALUES
(10),(20),(30),(40)

SELECT SUM(ID) Sums FROM testSumTable
WHERE ID = 1

--

Now in all the cases we will get NULL as the sum output. Now in all the cases we will get NULL as the sum output. As far as I know SQL internally works with Bags(https://msbiskills.com/2015/09/15/sql-server-what-are-bags-sets/) not sets. Now if our bag doesn’t have anything why we are getting NULL and not 0. Now the question is why it so ? 

Please add your answers in the comments section. Enjoy !! We all need to learn.

MSBISKILLS.COM

Pawan Kumar Khowal

Advertisements