Query that counts multiple values

[LittleSam]

Hi,
I have a very basic question but I havent found answer to it anywhere.

I have a table that has following columns: date, answer1, answer2,
answer3, answer4
and entry: 2007-1-1, 'a', 'b', 'a', 'c'

What I want to do is count number of character "a":s as an answer.
Normally (if using COUNT, MATCH..AGAINST) mysql returns rows it finds.
I want to find out how many times this character "a" appears in a
record.
What to do? Thanks for helping.

Br,
Sam

[strawberry]

Post by LittleSam
Hi,
I have a very basic question but I havent found answer to it anywhere.
I have a table that has following columns: date, answer1, answer2,
answer3, answer4
and entry: 2007-1-1, 'a', 'b', 'a', 'c'
What I want to do is count number of character "a":s as an answer.
Normally (if using COUNT, MATCH..AGAINST) mysql returns rows it finds.
I want to find out how many times this character "a" appears in a
record.
What to do? Thanks for helping.
Br,
Sam

revise your structure

date, answer, result

2007-01-01,1,'a'
etc

select count(*) from table where result = 'a' group by date.

or, if you really must use your existing structure, just join the
table to itself 3 times - but the first option is a better solution.

[LittleSam]

Post by strawberry

Post by LittleSam
Hi,
I have a very basic question but I havent found answer to it anywhere.
I have a table that has following columns: date, answer1, answer2,
answer3, answer4
and entry: 2007-1-1, 'a', 'b', 'a', 'c'
What I want to do is count number of character "a":s as an answer.
Normally (if using COUNT, MATCH..AGAINST) mysql returns rows it finds.
I want to find out how many times this character "a" appears in a
record.
What to do? Thanks for helping.
Br,
Sam

revise your structure
date, answer, result
2007-01-01,1,'a'
etc
select count(*) from table where result = 'a' group by date.
or, if you really must use your existing structure, just join the
table to itself 3 times - but the first option is a better solution.

Thanks a lot for your advise!
I restructured my tables and now everything works as should.
Thanks again.

-Sam