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)

, ,
Previous Post
SQL Server – Find Distinct Result Sets Using EXCEPT Operator
Next Post
SQL SERVER – Measuring the Length of VARCHAR and NVARCHAR Columns with COL_LENGTH

Related Posts

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);

    Reply
  • 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)

    Reply
  • 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.

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

    Reply
  • 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

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

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

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

    Reply
  • 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

    Reply
  • 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

    Reply
    • Hi Srini. Sorry for late response. You are right – I made myself not understable. Thanks for taking your time to review it.

      Reply

Leave a Reply Cancel reply

Menu
Exit mobile version