Count two different columns

Question

So I have this table in my database

Item Cat1 Cat2 -------------------- Aaa Red Used Aaa Blu Used Bbb Gre New Bbb Blu New Ccc Gre New Ddd Blu Used 

and I want to show how many elements are red in one column, and New in another column, for example:

 Item Red New ------------------- Aaa 1 0 Bbb 0 2 Ccc 0 1 Ddd 0 0 

I know how to display them in two tables, but I don’t know how to combine them.

 $query = mysql_query("SELECT *, count(Item) AS CountItem FROM Table WHERE Cat1 = 'Red' GROUP BY Item"); $query2 = mysql_query("SELECT *, count(Item) AS CountItem2 FROM Table WHERE Cat2 = 'New' GROUP BY Item"); while($row = mysql_fetch_array($query) AND $row2 = mysql_fetch_array($query2)) { echo $row['CountItem'] . " " . $row2['CountItem2'] . " " . $row['Item']; echo "<br>"; } 

This does not work, as it shows only those elements that are marked as Aaa, and it’s hard for me to understand what I'm doing wrong here.

Answer 1

This is the job for SUM(CASE) :

 SELECT Item, SUM(CASE WHEN Cat1 = 'Red' THEN 1 ELSE 0 END) AS Red, SUM(CASE WHEN Cat2 = 'New' THEN 1 ELSE 0 END) AS New FROM Table GROUP BY Item 

The idea here is that for all Red lines you assign 1 (others get 0) and you add them 1 and 0 to get the score. Same thing for New values.

You can do the same with greater multiplicity, using also the MySQL 0/1 Boolean estimate:

 SELECT Item, SUM(Cat1 = 'Red') AS Red, SUM(Cat2 = 'New') AS New FROM Table GROUP BY Item 

In this example, Cat1 = 'Red' will return 1 if true, and those are summed up. The SUM(CASE) method will be more portable in RDBMS, except for MySQL, although if other systems handle their booleans differently.

Edit:

To clarify, in PHP you get them as $row['Red'] and $row['New'] . You can simply change the aliases to everything you need: AS Red to AS CountItem to suit your original ...

 while($row = mysql_fetch_array($query) AND $row2 = mysql_fetch_array($query2)) { echo $row['Red'] . " " . $row2['New'] . " " . $row['Item']; echo "<br>"; } 

Answer 2

 SELECT Item, SUM(CASE WHEN Cat1 = 'Red' THEN 1 ELSE 0 END) AS Red, SUM(CASE WHEN Cat2 = 'New' THEN 1 ELSE 0 END) AS New, FROM Table GROUP BY Item