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)
