What is a result of the query below for table with 10 rows when there are 2 clients with the headcount column having a NULL value, there are 7 customers with 1 head and 1 customer with 2 heads?
SELECT SUM(headcount) FROM clients
/*
ClientId
HeadCount
1
1
2
NULL
3
1
4
2
5
1
6
NULL
7
1
8
1
9
1
10
1
*/
Answer
Explanation
The result is 9 because if you use SUM(column), only rows with NOT NULL value in the column are summed.
Example
DECLARE @clients TABLE (headcount INT, name NVARCHAR(50))
INSERT INTO @clients (headcount, name) VALUES (1, '1 Peter Johnson')
INSERT INTO @clients (headcount, name) VALUES (1, '2 Peter Johnson')
INSERT INTO @clients (headcount, name) VALUES (1, '3 Peter Johnson')
INSERT INTO @clients (headcount, name) VALUES (1, '4 Peter Johnson')
INSERT INTO @clients (headcount, name) VALUES (1, '5 Peter Johnson')
INSERT INTO @clients (headcount, name) VALUES (2, '6 Peter Johnson')
INSERT INTO @clients (headcount, name) VALUES (1, '7 Peter Johnson')
INSERT INTO @clients (headcount, name) VALUES (1, '8 Peter Johnson')
INSERT INTO @clients (headcount, name) VALUES (NULL, NULL)
INSERT INTO @clients (headcount, name) VALUES (NULL, NULL)
SELECT SUM(headcount) FROM @clients
-- Returns 9, because rows with NULL values are ignored
Related Databases job interview questions
What is the processing order of logical parts of the following query?
DatabasesSQL MediorWhen you use SQL Server, what is a difference between local and global temporary table?
DatabasesMS SQL ServerSQL SeniorWhat is a result of the following query for table with 10 rows when there are 2 clients with the name column having a NULL value?
DatabasesSQL Medior