Q:

Select COUNT for each value

My table looks like this:
id    type    value
1    type1    Good
2    type1    Bad
3    type1    Match
4    type2    Good
5    type2    Bad
6    type2    Match
7    type3    Good
8    type3    Bad
9    type3    Match

each 'type' have 3 possible value options
Currently I use this code to preview data
$result = mysql_query("SELECT type, value, COUNT(*) FROM data GROUP BY type,value ORDER BY COUNT(*) DESC"); 
echo "<table border='1'>";
while($row = mysql_fetch_array( $result )) {
echo "<tr>";
echo '<td><FONT SIZE=3 COLOR=BLACK> ' . $row['type'] .  '  </FONT></td>';
echo '<td><FONT SIZE=3 COLOR=BLACK> ' . $row['value'] .  '  </FONT></td>';
echo '<td><FONT SIZE=4 COLOR=GREEN> ' .$row['COUNT(*)'] . '</FONT></td>';}  
echo "<tr>";  
echo "</table>";

So, results from table its displayed in 3 column with many rows(3*3*3)
I am trying to display it in a table with 4 column and only 3 row
like this one:
type    COUNT "Good"    COUNT "Bad" COUNT "Match"
type1       1               1             1
type2       1               1             1
type3       1               1             1

and I am not sure how to do that.
Any idea how to do that?

A:

You can (ab)use a combination of SUM with CASE to do this:
SELECT type,
       SUM(CASE WHEN type='Good' THEN 1 ELSE 0 END) AS good,
       SUM(CASE WHEN type='Bad' THEN 1 ELSE 0 END) AS bad,
       SUM(CASE WHEN type='Match' THEN 1 ELSE 0 END) AS match
  FROM data
  GROUP BY type

This will group all values based on the type, then use SUM to find out how many of each values there are. By using CASE we either sum up zeroes or ones for each row, so it only adds up to one of the "counts."

