multiple independent where clauses

[weazy]

I am trying to count several columns if their values match a specific parameter

 

e.g.

 

select count(s1q1), count(s1q2), count(s1q3) ... FROM tablename WHERE s1q1 = 4 or s1q2 = 4 or s1q3 = 4 or ....

 

 

so if s1q1 has 10 entries that are a 4 and 6 entries that are other numbers I would expect to see a 10 same goes for the rest of the columns. however, I am not getting that kind of response I am getting wildly incorrect counts. Does anyone know the best way to do this? I have about 25 columns to count so I dont want to do a query for each column.

 

in short, what I am after here is to find out how many times s1q1 = 4, how many times s1q2 = 4, so on and so forth without running multiple queries

 

thanks

[Barand]

First rule of data normalisation - remove repeating items and put them in a separate table. A single query with a GROUP BY would then do the trick.

 

At present your s1q1 count includes records where s1q2=4 or s1q3 = 4 even though s1q1 contains something other than 4, which is why it is inaccurate.

 

This is untried but may work :-

 

SELECT SUM(IF (s1q1=4, 1,0)) as Count1, SUM(IF(s1q2=4,1,0)) as Count2, ......

 

hth