Summary query from several fields in SQL

Question

Data table structure is:
id1,id2,id3,id4,... (some other fields).
I want to create summary query to find out how many times some ID value is used in every column.

Data
1,2,3,4,2008
2,3,5,1,2008
1,3,2,5,2007
1,2,3,6,2007
3,1,2,5,2007

For value 1, the result should be
1,0,0,1,2008
2,1,0,0,2007

How to accomplish this with one query (in MySQL).

Answer 1

Use a characteristic or delta function:

DECLARE @look_for AS int
SET @look_for = 1

SELECT SUM(CASE WHEN id1 = @look_for THEN 1 ELSE 0 END) AS id1_count
    ,SUM(CASE WHEN id2 = @look_for THEN 1 ELSE 0 END) AS id2_count
    ,SUM(CASE WHEN id3 = @look_for THEN 1 ELSE 0 END) AS id3_count
    ,SUM(CASE WHEN id4 = @look_for THEN 1 ELSE 0 END) AS id4_count
FROM tbl

There are ways to code generate this (also a technique using PIVOT and UNPIVOT in SQL Server which is not ANSI) based on your table and the distinct ID values also.

Answer 2

if X is the id value you're looking for, you'd do it something like this.

select (select count(*) where id1 = X) as countid1 ... etc

Answer 3

select

(select count(id1) from t1 where id1 = @param) as id1,

(select count(id2) from t2 where id2 = @param) as id2

Answer 4

This seems like the best solution (from Wiki):

select years,
sum(1*(1-abs(sign(id1-56)))) as id1,
sum(1*(1-abs(sign(id2-56)))) as id2,
sum(1*(1-abs(sign(id3-56)))) as id3,
sum(1*(1-abs(sign(id4-56)))) as id4,
from mytable
group by years