0

i want to create a table having years as rows and 3 columns where the values would be the number of projects for each year where the revenue is below 500k, between 500k and 1M, greater than 1M. i was told to try the Case statment but i really can't get it done. i m using mysql

my fields are fiscalYear and AdjRevenue1YCommited from financials_tbl.

i started aiming to get just 3 columns (year,below500,between 500 and 1M)

select fiscalYear,

    Case
        When AdjRevenue1YCommited < 500000 then count(AdjRevenue1YCommited)
        Else null
        end as b500,

    Case
        When AdjRevenue1YCommited between 500000 and 1000000  then count(AdjRevenue1YCommited)
        Else null
        end as a500

    from financials_tbl
    group by fiscalYear
Federico Sanchez
  • 145
  • 1
  • 2
  • 12

1 Answers1

1

Try this:

select fiscalYear,
       sum(AdjRevenue1YCommited < 500000) as b500,
       sum(AdjRevenue1YCommited between 500000 and 1000000) as a500
from financials_tbl
group by fiscalYear
Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
  • Giorgos your answer worked but i don't understand why you used Sum instead of count. Count bring identical values in both columns which is wrong – Federico Sanchez Mar 28 '16 at 22:04
  • @FedericoSanchez Using `SUM` the result is incremented by 1 each time the predicate inside the parentheses is true. The predicates used are totally different, so I can't see how the results could be the same. – Giorgos Betsos Mar 29 '16 at 05:46