SQL Tricky Interview Question | Number of Occurrences of a character in All Columns

[@bandlarohithrayal8221]

Superb explanation 👌

[@praveenkumarbommisetty6093]

Thank You 😊

[@annepuappalaraju145]

❤

[@user-ov2kv6ez6e]

Marvelous explanation

[@praveenkumarbommisetty6093]

Thank You 😊

[@javeed939]

super explanation sir :)

[@praveenkumarbommisetty6093]

Thank You 😊

[@bhargavkumar8522]

SUPER EXPLANATION BRO.
SELECT SUM(COLUMNS) FROM
(SELECT COUNT(*) COLUMNS FROM TEST1
WHERE SUBSTR(COL1,1,1)='P'
UNION ALL
SELECT COUNT(*) FROM TEST1
WHERE SUBSTR(COL2,1,1)='P'
UNION ALL
SELECT COUNT(*) FROM TEST1
WHERE SUBSTR(COL3,1,1)='P'
UNION ALL
SELECT COUNT(*) FROM TEST1
WHERE SUBSTR(COL4,1,1)='P');
SELECT SUM(COL) AS "NUMBER OF OCCURANCES" FROM
(SELECT COUNT(*) COL FROM TEST1 WHERE COL1 LIKE '%P%'
UNION ALL
SELECT COUNT(*) FROM TEST1 WHERE COL2 LIKE '%P%'
UNION ALL
SELECT COUNT(*) FROM TEST1 WHERE COL3 LIKE '%P%'
UNION ALL
SELECT COUNT(*) FROM TEST1 WHERE COL4 LIKE '%P%');

[@praveenkumarbommisetty6093]

Nice Approaches😊

[@shashankm2859]

Nicely explained... I have one question, what if you 500 columns in a table then your query will become much longer... Is there any way you can concatenate all columns of the table without writing each column name?

[@praveenkumarbommisetty6093]

We can take it from Information_schema.Columns Views and we can make it dynamic

[@snehsparsh7954]

-- Number of Occurrences of a character in All Columns
-- 1st way using UNION in SQL SERVER
SELECT SUM(tmp.cnt_of_P) AS [Num of occurences of P]
FROM
(
SELECT COUNT(*) AS cnt_of_P FROM TEST_T WHERE COL1 = 'P'
UNION ALL
SELECT COUNT(*) AS cnt_of_P FROM TEST_T WHERE COL2 = 'P'
UNION ALL
SELECT COUNT(*) AS cnt_of_P FROM TEST_T WHERE COL3 = 'P'
UNION ALL
SELECT COUNT(*) AS cnt_of_P FROM TEST_T WHERE COL4 = 'P'
) tmp ;
SELECT COUNT(*) AS [Num of occurences of P]
FROM
(
SELECT COL1 FROM TEST_T
UNION ALL
SELECT COL2 FROM TEST_T
UNION ALL
SELECT COL3 FROM TEST_T
UNION ALL
SELECT COL4 FROM TEST_T
) tmp
WHERE
TMP.COL1 = 'P';
-- 2nd way using LEN, REPLACE and CONCAT in SQL SERVER
SELECT
SUM(
LEN(CONCAT(COL1,COL2,COL3,COL4)) - LEN(REPLACE(CONCAT(COL1,COL2,COL3,COL4), 'P', ''))
) AS [Num of occurences of P]
FROM
TEST_T
-- 3rd way using STRING_AGG in SQL SERVER
SELECT
LEN(STRING_AGG(CONCAT(COL1,COL2,COL3,COL4), ''))
-
LEN(REPLACE(STRING_AGG(CONCAT(COL1,COL2,COL3,COL4), ''), 'P', ''))
AS [Num of occurences of P]
FROM
TEST_T
-- 4th way using CASE WHEN statements in SQL SERVER
SELECT
(
SUM(CASE WHEN COL1 = 'P' THEN 1 ELSE 0 END)
+
SUM(CASE WHEN COL2 = 'P' THEN 1 ELSE 0 END)
+
SUM(CASE WHEN COL3 = 'P' THEN 1 ELSE 0 END)
+
SUM(CASE WHEN COL4 = 'P' THEN 1 ELSE 0 END)
) AS [Num of occurences of P]
FROM
TEST_T

[@praveenkumarbommisetty6093]

Nice 🙂