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)
11 Comments. Leave new
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);
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)
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.
SELECT SUM(IIF(COL1 =1 ,1,0)+IIF(COL2 =1 ,1,0)+IIF(COL3 =1 ,1,0)) AS COUNTS FROM #TEST
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
Wouldn’t method 2 return incorrect result if you were counting instances of 2?
An other way to bring the columns together would be pivot/unpivot.
SELECT SUM(col_value)
FROM #test
UNPIVOT (col_value FOR id IN (col1,col2,col3) ) as pvt
WHERE col_value=1
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
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
Hi Srini. Sorry for late response. You are right – I made myself not understable. Thanks for taking your time to review it.