multiple count in mysql/php

Question

i have a litte question.. i got a mysql-table

cat | item |  data |
100 |   0  |   10  |
102 |   1  |   3   |
101 |   1  |   1   |
100 |   0  |   40  |
100 |   1  |   20  |
102 |   0  |   3   |
101 |   1  |   2   |
100 |   0  |   30  |
102 |   1  |   3   |

and my query looks like this

$query = "SELECT cat, COUNT(item) FROM table WHERE item=0 GROUP BY cat";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
    echo $row['COUNT(item)'].''.$row['COUNT(cat)'];
}

how does it work that i count item by 0 and 1 at the same time?

i want it like this

categories |   items 0  |   items 1
100        |     3      |     1 
101        |     0      |     2 
102        |     1      |     2 

sorry for my bad english :/ hope you understand my problem

best regards bernte

---

Thanks for helping james_bond and bfavaretto

is there a whay to do an other rule? i want the sum of data for item 0 and item 1

categories |   data 0  |   data 1
100        |     80    |     20 
101        |     0     |     3 
102        |     3     |     6

i tried it with the code of james_bond without success :(

best regards bernte

Answer 1

$query = "SELECT cat, SUM(CASE WHEN item=0 THEN 1 ELSE 0 END) AS items_0, SUM(item) AS items_1 FROM table GROUP BY cat";