how to query this?

[PHPycho]

Hello forums!
I would like to know how to count the no of fields that has specific values.
for example:
"table1" has field called "filed1" and it has values
------
¦field1¦
-------
¦X ¦
¦X ¦
¦Y ¦
¦ X ¦
:
:
etc
I would like to know the no of rows that has the value X using count...
How to perform such query..
thanks in advance to all of you

[vincevincevince]

SELECT COUNT(*) as n FROM table1 where field1 = 'X'

[PHPycho]

Pardon me
Cant we do without using where condition, ie what i want
Thanks again

[vincevincevince]

SELECT COUNT(*) as n, field1 FROM table1 GROUP BY field1

[LifeinAsia]

Cant we do without using where condition, ie what i want

Why do you want to do it without the WHERE condition? It's the easiest way to implement it. Especially since if looks like your Field1 field is a VARCHAR that sometimes has extraneous spaces, so neither example given so far will yield the correct count.

This will get you what you want:
SELECT COUNT(*)
FROM Table1
WHERE Field1 LIKE '%X%'

[PHPycho]

Hello forums!
I would like to
For example:
table "table_name" contains fields like
"field1" which contains only values "A","B"
"field2" which contains only values "X","Y"
To find the no of A in field1, no of B in field1 , no of X in field2, no of Y in field2 we can perform independent query as:
$sql1 = "SELECT count(field1) AS no_of_A FROM table_name WHERE field1='A'";
$sql2 = "SELECT count(field1) AS no_of_B FROM table_name WHERE field1='B'";
$sql3 = "SELECT count(field2) AS no_of_X FROM table_name WHERE field1='X'";
$sql4 = "SELECT count(field2) AS no_of_Y FROM table_name WHERE field1='Y'";

But I want to perform above queries in one......
I would like to have somewhat
$sql = "SELECT count(field1 = 'A') AS no_of_A, count(field1 = 'B') AS no_of_B, count(field2 = 'X') AS no_of_X),count(field2 = 'Y') AS no_of_Y FROM table_name WHERE field1='Y'"
I know above query doesnt work..i am just telling what i want to do..
and thats the very challenging for me..
Any comments and suggestions are warmly welcome
I am awaiting for your help.
Thanks in advance to all of you

[mcibor]

Then Vince's method will work:

SELECT COUNT(*) as n, field1 FROM table1 GROUP BY field1
will return:
4, A
6, B

SELECT COUNT(*) as n, field2 FROM table1 GROUP BY field2
will return:
2, X
14, Y

I don't know of any way to combine these two into one and still to return correct values.
Michal