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 shows how many Items are Red in one column and New in an other column, like this:

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 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 doesn't seem to work as this only shows Items that are labeled Aaa and I'm having trouble understanding what I am doing wrong here.

Answer 1

This is a 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 the Red rows, you assign a 1 (others get a 0) and you add up those 1's and 0's to get the count. Same thing for the New values.

You can do the same thing with more brevity by exploiting MySQL's 0/1 boolean evaluation too:

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

In this example, Cat1 = 'Red' will return a 1 if true, and those get summed up. The SUM(CASE) method is going to be more portable across RDBMS other than MySQL though if other systems treat their booleans differently.

Edit:

Just to clarify then, in PHP you will retrieve these as $row['Red'] and $row['New']. You can just change the aliases to whatever you need: AS Red to AS CountItem to match your original...

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