Jump to content


Photo

multiple independent where clauses


  • Please log in to reply
1 reply to this topic

#1 weazy

weazy
  • Members
  • Pip
  • Newbie
  • 1 posts

Posted 24 July 2003 - 02:37 PM

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

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 27 July 2003 - 04:59 PM

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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users