Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

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 Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ 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/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

Advertisements