2

I have a table called inventory that includes two columns as follows:

enter image description here

I would like to query this table to return a result set that lists how many items of each condition I have (column headers show in example below are not wanted in result set...just show here to give clarity):

enter image description here

I guess it would look something like:

  SELECT item, sum(condition???), sum(condition???), sum(condition???) 
    FROM inventory 
GROUP BY item

How can I accomplish this? Thanks.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
user1218172
  • 193
  • 1
  • 12
  • 2
    You're almost there - here's the [MySQL documentation for Control of Flow syntax](http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html) – OMG Ponies Nov 03 '12 at 23:41

1 Answers1

6

You were very close:

SELECT item, 
  sum(case when `condition` = 'poor' then 1 else 0 end) as poor, 
  sum(case when `condition` = 'fair' then 1 else 0 end) as fair, 
  sum(case when `condition` = 'good' then 1 else 0 end) as good
FROM inventory 
GROUP BY item

See SQL Fiddle with Demo

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • Thank you bluefeet! This is perfect! I appreciate the time to point me in the right direction. – user1218172 Nov 03 '12 at 23:51
  • @user1218172 you are welcome, you were very close to the solution on your own, you just needed a little help. – Taryn Nov 03 '12 at 23:53