CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Krish
Krish

Reputation: 176

How find the occurrences of same value in different columns using a query

When I was attending an interview I have faced an interesting question, which is listed below The question is given below

How to find out the occurrence of the value 1 in all the columns

DECLARE @tbl AS TABLE(Col1 INT, Col2 INT, Col3 INT)
INSERT INTO @tbl
VALUES(1,1,1),(1,2,1),(1,1,3)

I have shared the answer like below. Not sure whether this is a proper answer.

SELECT (SELECT COUNT(1) FROM @tbl WHERE COL1 = 1) +
(SELECT COUNT(1) FROM @tbl WHERE COL2 = 1)+
(SELECT COUNT(1) FROM @tbl WHERE COL3 = 1)

Please let me know whether is there any other way to find out the proper answer

Upvotes: 2

Views: 263

Answers (4)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

One more approach uses the great abilities of XML and XQuery to deal with generic data. It will be fully generic when you pass in the value as sql:column() or sql:variable instead of the hardcoded "1". This approach would not even need to know the count and names of the columns in advance:

DECLARE @tbl AS TABLE(Col1 INT, Col2 INT, Col3 INT);
INSERT INTO @tbl
VALUES(1,1,1),(1,2,1),(1,1,3);

--Find a row where all values are 1

SELECT 
(
    SELECT * FROM @tbl FOR XML PATH('row'),ROOT('tbl'),TYPE
)
.query('/tbl/row[not(*/text()!="1")]');

--Count the 1s

SELECT *
      ,x.value('count(/row/*[text()="1"])','int')
FROM @tbl t
CROSS APPLY(SELECT t.* FOR XML PATH('row'),TYPE) AS A(x)

Upvotes: 1

Stanislav Kundii
Stanislav Kundii

Reputation: 2894

SELECt a.*, b.x  FROM @tbl a
OUTER APPLY
(   
    SELECT TOP 1 1
    FROM (VALUES(a.Col1),(a.Col2),(a.Col3)) c(a)
    WHERE   
        c.a = 1
) b(x)

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use apply :

select cols, count(*) as occurrence 
from @tbl cross apply 
     ( values ('col1', col1), ('col2', col2), ('col3', col3)
     ) t (cols, val)
where val = 1
group by cols;

Upvotes: 7

Gopakumar N.Kurup
Gopakumar N.Kurup

Reputation: 936

Just sharing an answer came in my mind:) Don't know how far it is correct.

DECLARE @tbl AS TABLE(Col1 INT, Col2 INT, Col3 INT)
INSERT INTO @tbl
VALUES(1,1,1),(1,2,1),(1,1,3)
SELECT SUM(CASE WHEN Col1 =1 THEN 1 ELSE 0 END)+SUM(CASE WHEN Col2 =1 THEN 1 ELSE 0 END)+SUM(CASE WHEN Col3 =1 THEN 1 ELSE 0 END) FROM @tbl WHERE (Col1 = 1 OR Col2 =1 OR COl3=1)

Upvotes: 1

Related Questions