SQL Puzzle | Count total occurrence of a character in all columns | SQL Interview Question

In this puzzle you have to count value a from all the columns preferably with a single select keyword. Please check out the sample input and the expected output.

Sample Input

a1 a2 a3
a d a
c e f
a a s
a   a
a a s

Expected Output

a1 a2 a3 CountOfA’s
a d a 8
c e f 8
a a s 8
a   a 8
a a s 8

Script – DDL and INSERT Sample Data

 ```-- CREATE TABLE Counta ( a1 VARCHAR(1) , a2 VARCHAR(1) , a3 VARCHAR(1) ) GO INSERT INTO Counta VALUES ('a','d','a'), ('c','e','f'), ('a','a','s'), ('a','','a'), ('a','a','s') GO SELECT * FROM Counta GO -- ```

–SOLUTION 1 : THE SUM … OVER() WITH IIF

 ```-- SELECT * ,SUM(IIF(a1='a',1,0)) OVER() +SUM(IIF(a2='a',1,0)) OVER() +SUM(IIF(a3='a',1,0)) OVER() [CountOfA's] FROM Counta -- ```

OUTPUT – 1

 ```-- a1 a2 a3 CountOfA's ---- ---- ---- ----------- a d a 8 c e f 8 a a s 8 a a 8 a a s 8 (5 rows affected) -- ```

–SOLUTION 2 : THE SUM … OVER() WITH CASE

 ```-- SELECT * ,SUM(CASE WHEN a1='a' THEN 1 END) OVER() +SUM(CASE WHEN a2='a' THEN 1 END) OVER() +SUM(CASE WHEN a3='a' THEN 1 END) OVER() [CountOfA's] FROM Counta -- ```

OUTPUT

 ```-- a1 a2 a3 CountOfA's ---- ---- ---- ----------- a d a 8 c e f 8 a a s 8 a a 8 a a s 8 (5 rows affected) -- ```

SOLUTION 3 : The SELECT WITH ANOTHER SELECT

 ```-- SELECT * , (SELECT COUNT(1) FROM Counta WHERE a1='a') +(SELECT COUNT(1) FROM Counta WHERE a2='a') +(SELECT COUNT(1) FROM Counta WHERE a3='a') [CountOfA's] FROM Counta -- ```

OUTPUT

 ```-- a1 a2 a3 CountOfA's ---- ---- ---- ----------- a d a 8 c e f 8 a a s 8 a a 8 a a s 8 (5 rows affected) -- ```

–SOLUTION 4 : The CROSS APPLY | No question can be completed without CROSS APPLY

 ```-- SELECT a.* , x1.cnt + x2.cnt + x3.cnt [CountOfA's] FROM Counta a CROSS APPLY ( SELECT COUNT(1) cnt FROM Counta WHERE a1='a' )x1 CROSS APPLY ( SELECT COUNT(1) cnt FROM Counta WHERE a2='a' )x2 CROSS APPLY ( SELECT COUNT(1) cnt FROM Counta WHERE a3='a' )x3 -- ```

OUTPUT

 ```-- a1 a2 a3 CountOfA's ---- ---- ---- ----------- a d a 8 c e f 8 a a s 8 a a 8 a a s 8 (5 rows affected) -- ```

–SOLUTION 5 : The CROSS APPLY WITH VALUES | My Fav Again

 ```-- SELECT a.*,SUM(b.cnt) OVER() [CountOfA's] FROM Counta a CROSS APPLY ( SELECT COUNT(*) cnt FROM (VALUES (a1),(a2),(a3)) as t(v) WHERE t.v = 'a' )b -- ```

OUTPUT

 ```-- a1 a2 a3 CountOfA's ---- ---- ---- ----------- a d a 8 c e f 8 a a s 8 a a 8 a a s 8 (5 rows affected) -- ```

–SOLUTION 6 : The CROSS APPLY WITH XML

 ```-- SELECT a.* ,SUM(v.value('count(/row/*[text()="a"])','TINYINT')) OVER() [CountOfA's] FROM Counta a CROSS APPLY(SELECT a.* FOR XML PATH('row'),TYPE) AS t(v) -- ```

OUTPUT

 ```-- a1 a2 a3 CountOfA's ---- ---- ---- ----------- a d a 8 c e f 8 a a s 8 a a 8 a a s 8 (5 rows affected) -- ```

Enjoy 🙂

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Performance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/