tags:

views:

495

answers:

4

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).

+1  A: 

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.

Cade Roux
+1 Cade. Riho is a Mysql'er and won't understand.
David B
Add a group by year for completeness.
jms
A: 

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
Dan Sydner
A: 

select

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

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

mson
Unless your table is indexed individually on eash of the id columns and sparse, the characteristic function version (which has been voted down) will outperform this in the majority of cases, since this will perform multiple table scans.
Cade Roux
+1  A: 

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
Riho