mysql query question count() function

[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 database 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 columnts. however, I am not getting that kind of response. 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.

thanks

[stolzyboy]

<?
$query = "select s1q1, s1q2, s1q3 from tablename where s1q1 = 4, s1q2 = 4, s1q3 = 4;";
$result = mysql_query($query) or die(mysql_error());

echo mysql_num_rows($result);
?>

[weazy]

that doesnt work:

SELECT count( s1q1 ) , count( s1q2 )
FROM tablename
WHERE s1q1 = 4, s1q2 = 4

gives:

You have an error in your SQL syntax near ' s1q2 = 4' at line 3

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, reason being, I have about 25 of those to find.

[stolzyboy]

sorry was in a hurry when i did that, this line

$query = "select s1q1, s1q2, s1q3 from tablename where s1q1 = 4, s1q2 = 4, s1q3 = 4;";

should be

$query = "select s1q1, s1q2, s1q3 from tablename where s1q1 = 4 or s1q2 = 4 or s1q3 = 4;";

but then again, you should be able to figure that out yourself

[weazy]

i tried that before I posted originally, in fact, if you look at my original post you will see that i tried the OR statement. your flame was misplaced.

the response i get is a wrong count when I use the or statement that way.

says
count(s1q1) = 9
count(s1q2) = 9

when actually there are 8 s1q1 and 7 s1q2

i am not trying to count the number of total results, that is why I dont run the general query then count the results using mysql_num_rows, that's not good enough. I need to know how many of each.

[stolzyboy]

i'll look into it further, i was confused, and it wasn't a flame, it was called "Constructive Criticism", and apparently you can't take it

Try reading text with different emphasis on words, and you can come up with just about every mood you could think of just dependent on what you read it as!

[weazy]

i've always thought of constructive criticism as a situation where the feedback is helpful, saying "you should be able to figure that out yourself" is not what I would consider helpful since you have no clue what my skill levels or experience are.

[stolzyboy]

well obviously, since you are construction queries and such, you should see my hurried syntax mistake with the commas instead of or/and's, but whatever, it's constructive in that it is supposed to make you try and troubleshoot yourself, hence you will be helping your learning by that method of self-help

ya see, help yourself, and you will learn

now i don't mean that noone will help you, but sometimes the best teacher is yourself (especially in programming)

🙂

still searching on your problem, is there an unlimited number of s1q3 or does it stop at say, s1q45 or something

[weazy]

well it goes like:

s1q1, s1q2, .... s1q10, s2q1, s2q2, .... s2q10, s3q1, s3q2, s3q3 ... s3q10

dont get me wrong, i appreciate your help.

[tasistro]

Try>

// I'm guessing you have a primary key called ID, if not so replace with appropiate value

select count(ID) as amount, s1q1, s1q2 ... FROM database WHERE s1q1 = 4 or s1q2 = 4 or s1q3 = 4 or .... GROUP BY s1q1, s1q2, ... DESC

You will then have to iterate for every row of returned values checking the values of s1q1, s1q2, etc fields and see the value in the amount field.

[BlueMeteor]

weazy,I read your post several times, Somewhere in your post I'm still cant understand ...sorry for my poor English...

But the follow SQL maybe what's you want..just try

hth🙂

=====
select
count(case s1q1 when 4 then 1 else null end) as s1q1_Count,
count(case s1q2 when 4 then 1 else null end)as s1q2_Count,
count(case s1q3 when 4 then 1 else null end)as s1q3_Count,
count(case s1q4 when 4 then 1 else null end)as s1q4_Count,
.....

FROM database

[tomhath]

BlueMeteor's suggestion is close to how I'd do it, but watch out for NULL values. I'd use 0 instead of null:

select 
count(case s1q1 when 4 then 1 else 0 end) as s1q1_Count, 
count(case s1q2 when 4 then 1 else 0 end) as s1q2_Count, 
count(case s1q3 when 4 then 1 else 0 end) as s1q3_Count, 
count(case s1q4 when 4 then 1 else 0 end) as s1q4_Count, 
..... 
FROM database 
where 4 in (s1q1 , s1q2 , s1q3 , s1q4)

Along the lines of constructive criticism, this is way too complicated because your table has an array inside each row (all those s1qx fields are really an array). It would be much better to have each value in a separate row. Then your query would be very simple:

myTable
qtype qvalue
s1q1      4
s1q2      2
s1q3      6
s1q3      4
s1q4      4
...

select qtype, count(*)
from myTable
where qvalue = 4
group by qtype

[weazy]

i agree, tat would have been much easier, but the way the data gets inputted, i think it was necessary to do it as such.