SQL SERVER – How to Count a Particular Value Across All Columns?

This question came up in the recent SQL Server Performance Tuning Practical Workshop. During the workshop, I have answer questions of users during lunch break. One of the users had a very interesting scenario where he had to change one of their employee’s email address for technical reasons but before he changes that he needed to count in every single place where the email exists. I have simulated the same situation in this blog post and we will see how we can count a particular value across all the columns of the table. Let us see the sample data here:

Col1        Col2        Col3
----------- ----------- -----------
1           1           1
1           2           1
1           1           3

In the sample data above we need to identify the number of digit 1 and count them. As you can see in the result above the numbers of 1 are total 7.

Let us first sample data and see the solution for the same.

USE TEMPDB
GO
CREATE TABLE #TEST
(
COL1 INT,
COL2 INT,
COL3 INT
);
INSERT INTO #TEST(COL1,COL2,COL3)
SELECT 1, 1, 1 UNION ALL
SELECT 1, 2, 1 UNION ALL
SELECT 1, 1, 3

Now let us explore two different methods which will count the value 1 in the table and produce the necessary value 7.

Method 1: Use CASE Expression

SELECT
SUM
(
CASE WHEN COL1=1 THEN 1 ELSE 0 END +
CASE WHEN COL2=1 THEN 1 ELSE 0 END +
CASE WHEN COL3=1 THEN 1 ELSE 0 END
) AS COUNTS
FROM #TEST

Method 2: Use UNION ALL operator

SELECT
COUNT(COL1) AS COUNTS FROM
(
SELECT COL1 FROM #TEST WHERE COL1=1
UNION ALL
SELECT COL2 FROM #TEST WHERE COL2=1
UNION ALL
SELECT COL3 FROM #TEST WHERE COL3=1
) AS T

Both the above methods produce the following result 7.

Method 1 simply counts when each column is equal to 1 and method 2 filter the results with each column equal to 1 and SUM function sums up the values

Let me know if you know any other simple methods in the comments section and I will publish it with due credit to you.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Related Posts

• elaenabakman
December 28, 2018 11:13 am

This here seems to perform better than the UNION Version listed in the post:

SELECT SUM(CALC.COUNTS)
FROM #TEST T
CROSS APPLY
(SELECT IIF(T.COL1 = 1, 1, 0) AS COUNTS
UNION ALL
SELECT IIF(T.COL2 = 1, 1, 0) AS COUNTS
UNION ALL
SELECT IIF(T.COL3 = 1, 1, 0) AS COUNTS) CALC (COUNTS);

• nobody
December 28, 2018 2:29 pm

Let SQL count itself:
SELECT
(SELECT COUNT(*) FROM #TEST WHERE COL1=1) +
(SELECT COUNT(*) FROM #TEST WHERE COL2=1) +
(SELECT COUNT(*) FROM #TEST WHERE COL3=1)

• AmateurSQL
December 28, 2018 3:34 pm

Method 1: Rather than many CASE statments it seems more efficient making the DB engine count:

SELECT (SELECT COUNT(*) FROM #TEST WHERE COL1=1) +
(SELECT COUNT(*) FROM #TEST WHERE COL2=1) +
(SELECT COUNT(*) FROM #TEST WHERE COL3=1)

— — —

Method 2: Only works because you are counting “1” which is the very same as adding them. Wouldn’t work for any other searched value. E.g. “2” would return “2” and “3” would return “3” although there is just one of each.
Probaly what you where trying to do is:

SELECT SUM(a) FROM
(SELECT COUNT(*) a FROM #TEST WHERE COL1=1 UNION ALL
SELECT COUNT(*) FROM #TEST WHERE COL2=1 UNION ALL
SELECT COUNT(*) FROM #TEST WHERE COL3=1) T

Or even:

SELECT COUNT(COL1) FROM (
SELECT COL1 FROM #TEST WHERE COL1=1 UNION ALL
SELECT COL2 FROM #TEST WHERE COL2=1 UNION ALL
SELECT COL3 FROM #TEST WHERE COL3=1) T

— — —

Regards.

• Gunasundari
December 28, 2018 6:16 pm

SELECT SUM(IIF(COL1 =1 ,1,0)+IIF(COL2 =1 ,1,0)+IIF(COL3 =1 ,1,0)) AS COUNTS FROM #TEST

• Elkin
December 29, 2018 2:11 am

I believe that in Method 2 it would be better to use Count aggregate instead of Sum, as Sum would only work for this particular value: 1

• Rob
December 29, 2018 6:03 am

Wouldn’t method 2 return incorrect result if you were counting instances of 2?

• Willi
December 29, 2018 1:06 pm

An other way to bring the columns together would be pivot/unpivot.

• djeos
December 31, 2018 12:48 pm

SELECT SUM(col_value)
FROM #test
UNPIVOT (col_value FOR id IN (col1,col2,col3) ) as pvt
WHERE col_value=1

• Srini
January 3, 2019 8:07 pm

Hi AmateurSQL,

Why the Method 2 won’t work for search criteria’s other than 1? Can you explain, why do you think it won’t work. As per my understanding, it should work irrespective of the search criteria being passed.

For example, if you are replacing 1 with 2 in Method 2 then it is

SELECT SUM(a) FROM
(SELECT COUNT(*) a FROM #TEST WHERE COL1=2 UNION ALL
SELECT COUNT(*) FROM #TEST WHERE COL2=2 UNION ALL
SELECT COUNT(*) FROM #TEST WHERE COL3=2) T

The above code basically does the summation of the counts irrespective of the values being passed and evaluates to expected output. How come this will produce 2 as an output instead of 1?

The same is the case with 3 being passed as search criteria, which results in 1 as opposed to 3 as mentioned by you.

Thanks,
Srinivas

• Srini
January 3, 2019 8:17 pm

Hi AmateurSQL,

Sorry for the confusion. I thought you are referring to Method 2 of yours won’t work but actually referring to Method 2 of Pinal’s solution.Yes, Method 2 of Pinal solution won’t work for other search criteria’s other than 1.

Thanks,
Srini

• 