trying to count a value in multi column mysql php?

Question

Look at my table below, I am trying to count the A in the table. A will only appear in one row, but sometime will be in the first column, sometime will be in other column, so I use OR, but it echo a number larger than 3, I only have 3 A, why? How can I only count 3 A?

"SELECT COUNT(*) FROM $table WHERE first='A' OR second='A' OR third='A' AND number <= 7"

id  first  second third  number
1     A      C      B      3
2     C      d      F      5
3     A      c      b      6
4     B      A      c      7

It sounds like you want WHERE (first='A' OR second='A' OR third='A') AND number <= 7 — Tristan
– Tristan, Commented Jan 29, 2016 at 6:05
@shmosel I think parameter in bracket must be a value not the column name. Correct me if i am wrong. — Mr. Engineer
– Mr. Engineer, Commented Jan 29, 2016 at 6:12
Can it be possible that A comes twice or more in single row? — Abhishek
– Abhishek, Commented Jan 29, 2016 at 6:37

mazedlx · Accepted Answer · 2016-01-29 06:16:13Z

1

"select count(*) 
 from $table 
 where (first = 'A' or second = 'A' or third = 'A') 
 and number <= 7";

answered Jan 29, 2016 at 6:16

mazedlx

1,45617 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Angel · Accepted Answer · 2016-01-29 06:58:30Z

1

SELECT 
(select count(*) from tablename where first LIKE '%A' and number<=7)
+
(select count(*) from tablename where second LIKE '%A' and number<=7)
+
(select count(*) from tablename  where third LIKE '%A' and number<=7);

try this code ,am sure you wil definitely get the right count

edited Jan 29, 2016 at 6:58

answered Jan 29, 2016 at 6:39

Angel

6126 silver badges24 bronze badges

Comments

devpro · Accepted Answer · 2016-01-29 06:40:41Z

0

You are going right just need to do two things:

Combine OR condition into brackets ()
Add backticks for MYSQL Keywords (second is a MYSQL Keywords)

Modified Query:

"SELECT COUNT(*) FROM $table 
WHERE (`first` = 'A' OR `second` = 'A' OR `third` = 'A') 
AND `number` <= 7"

answered Jan 29, 2016 at 6:40

devpro

16.1k3 gold badges29 silver badges39 bronze badges

Comments

Abhishek · Accepted Answer · 2016-01-29 06:41:49Z

0

Try this if A occurs more than on single row:

select sum(if(first = 'A',1,0)+if(second = 'A',1,0)+if(third = 'A',1,0)) as count
from $table where number <= 7;

SQL Fiddle:http://sqlfiddle.com/#!9/f34a8/3

answered Jan 29, 2016 at 6:41

Abhishek

4,8094 gold badges29 silver badges39 bronze badges

Collectives™ on Stack Overflow

trying to count a value in multi column mysql php?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related