mysql - Regrouping Multiple rows into one -

let's have table using query

i'm working on query can give

here's i've tried far :

select [first name], [last name], club, case when competition='la liga' wins end [la liga],  case when competition='copa del rey' wins end [copa del rey], case when competition='supercopa de espana' wins end [supercopa de espana],      case when competition='uefa champions league' wins end [uefa champions league],   case when competition='uefa super cup' wins end [uefa super cup],   case when competition='fifa club world cup' wins end [uefa super cup]  dbo.table group [first name], [last name], club 

but i'm getting 'sql execution error' column 'dbo.wins' invalid in select list because not contained in either aggregate function or group clause. column 'dbo.competition' invalid in select list because not contained in either aggregate function or group clause.

putting columns 'wins' , 'competition' in group clause don't give result want.

any other suggestions?

thanks in advance

you need add sum, min, or max it, this:

select [first name], [last name], club, sum(case when competition='la liga' wins end) [la liga],  sum(case when competition='copa del rey' wins end) [copa del rey], sum(case when competition='supercopa de espana' wins end) [supercopa de espana],      sum(case when competition='uefa champions league' wins end) [uefa champions league],   sum(case when competition='uefa super cup' wins end) [uefa super cup],   sum(case when competition='fifa club world cup' wins end) [uefa super cup]  dbo.table group [first name], [last name], club 

you cannot leave un-aggregated items in group query in sql server, though know there @ 1 non-null entry each item.