Have you failed a job interview?
Send us your job interview questions and get correct answers.

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

 

*/

Experience Level: Medior
Tags: DatabasesSQL

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

Comments

No Comments Yet.
Be the first to tell us what you think.