I have a table with following values
City STATE COLOR REGION
city1 Texas White SW
city2 Texas Gray SW
city3 Texas Gray SW
city4 Nevada Gray PAC
c5 Texas Yellow SW
c6 Florida Yellow SE
c7 Missouri Orange W
c8 Texas Orange SW
I am trying to create a report which looks like:
STATE White Gray Yellow Orange
Texas 1 2 0 1
Nevada 0 1 0 0
Florida 0 0 1 0
Missouri 0 0 0 1
I need to do this in oracle pl/sql
I tried to write this query
select state,
SUM( decode(color,'Orange',count(state),0) ) as orange,
SUM( decode(color,'Yellow',count(state),0) ) as yellow,
SUM( decode(color,'Gray',count(state),0) ) as gray
SUM( decode(color,'White',count(state),0) ) as white
from my_table
group by color
This query gives me an error "ORA-00937: not a single-group group
function"
Please help !
any ideas???
