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

Open up another question, if you keep editing your question this will be an never ending situation — james_bond
– james_bond, Commented Jul 8, 2011 at 1:26

bfavaretto · Accepted Answer · 2011-07-06 03:04:22Z

7

$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";

answered Jul 6, 2011 at 3:04

bfavaretto

72k18 gold badges118 silver badges162 bronze badges

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

Comments

james_bond · Accepted Answer · 2011-07-06 03:20:13Z

4

SELECT  cat, SUM(IF(item = 0,1,0)) AS items0,  SUM(IF(item = 1,1,0)) AS items1 FROM table GROUP BY cat

answered Jul 6, 2011 at 3:20

james_bond

6,9163 gold badges30 silver badges39 bronze badges

1 Comment

bernte Over a year ago

thanks for the tip :D sorry i'm new with stackoverflow :D i asked the new question here... stackoverflow.com/questions/6630748/multiple-sum-for-row

Collectives™ on Stack Overflow

multiple count in mysql/php

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related