mysql - How to qualify the condition in a CASE expression (similar to WHERE clause) -

here table have

company  |  year |  amount ------------------------ companya | 2008  |   5 companya | 2008  |   4 companyb | 2008  |   4 companyb | 2009  |   1 companyc | 2009  |   4 companyc | 2010  |   2 

pseudo code

select company, case when year = 2008 select max(amount) each company year = 2008 case when year = 2009 select max(amount) each company year = 2009 group company 

i've been able case work it's selecting max(amount) of years , grouping company, need max per year.

i think need condition in case expression qualified clause or perhaps nested case can't work.

real code

select record_id record_id, invoice_date invoice_date, company_id company_id, company_name company_name, fiscal_year fiscal_year, (case fiscal_year when '2008' max(amount) else 0 end) `2008`, (case fiscal_year when '2009' max(amoutn) else 0 end) `2009`, (case fiscal_year when '2010' max(amount) else 0 end) `2010`, (case fiscal_year when '2011' max(amount) else 0 end) `2011`, (case fiscal_year when '2012' max(amount) else 0 end) `2012`, (case fiscal_year when '2013' max(amount) else 0 end) `2013` tbl group company_name order invoice_date desc, record_id desc; 

any mucho appreciated! thx

try instead:

select company,   max(case when year = 2008 amount else 0 end) '2008',   max(case when year = 2009 amount else 0 end) '2009' tbl group company; 

see in action:

• sql fiddle demo

this give you:

|  company | 2008 | 2009 | -------------------------- | companya |    5 |    0 | | companyb |    4 |    1 | | companyc |    0 |    4 |