Count two different columns

count, mysql, sql

Solution

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

Problem

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.

Original source