Count number of times a value appears in Multiple Columns of the same table.

[chillwill]

Is it possible to query for a specific value across multiple columns in
a single table and get a count of how many times that particular value
appears in a set of columns?

Example:

Table : info
Columns: |Col1|Col2|Col3|
Values: | 1 | 2 | 1 |

In mySQL or MSSQL, I am looking to create a query to count the number of times "1" appears in Col1, Col2 & Col3?
The result should be "2"

Can someone share some insight?

Thank you in advance...

-Will

[skhanal]

Count is a operation performed on set of rows, it can't be used for counting a value in columns. One option is to write a query to turn columns into rows and counting it (UNPIVOTING).

Something like

create table #t (id int identity(1,1), c1 int, c2 int, c3 int)

insert into #t values(1,1,2)
insert into #t values(1,2,2)
insert into #t values(1,3,2)
insert into #t values(1,3,3)
insert into #t values(1,1,1)
insert into #t values(2,2,2)
insert into #t values(1,3,2)
insert into #t values(1,1,2)
insert into #t values(1,2,2)
insert into #t values(1,3,2)


select * from #t

select g.id, g.c, count(*)
from
(select id, c1 as c
from #t
union all
select id, c2 as c
from #t
union all
select id, c3 as c
from #t) as g
where g.c=1
group by g.id, g.c