Jump to content

multiple independent where clauses


weazy

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.