CODEWITHSUNDEEP
mysqlsqllinqsql-server-2008-r2
Zack09
Zack09

Reputation: 103

Regrouping Multiple rows into one

Let's say I have this table that I get using a query

enter image description here

I'm working on a query that can give something like this

enter image description here

here's what I've tried so far :

Select [First Name], [Last Name], Club,
case when Competition='La Liga' then Wins end as [La Liga], 
case when Competition='Copa del Rey' then Wins end as [Copa del Rey],
case when Competition='Supercopa de Espana' then Wins end as [Supercopa de Espana],     
case when Competition='UEFA Champions League' then Wins end as [UEFA Champions League],  
case when Competition='UEFA Super Cup' then Wins end as [UEFA Super Cup],  
case when Competition='FIFA Club World Cup' then Wins end as [UEFA Super Cup]

From dbo.Table
Group by [First Name], [Last Name], Club

But I'm getting an 'SQL Execution Error' Column 'dbo.Wins' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Column 'dbo.Competition' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

putting the columns 'Wins' and 'Competition' in the GROUP BY clause don't give the result that i want.

Any other suggestions?

Thanks in advance

Upvotes: 2

Views: 124

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726879

You need to add SUM, MIN, or MAX to it, like this:

Select [First Name], [Last Name], Club,
SUM(case when Competition='La Liga' then Wins end) as [La Liga], 
SUM(case when Competition='Copa del Rey' then Wins end) as [Copa del Rey],
SUM(case when Competition='Supercopa de Espana' then Wins end) as [Supercopa de Espana],     
SUM(case when Competition='UEFA Champions League' then Wins end) as [UEFA Champions League],  
SUM(case when Competition='UEFA Super Cup' then Wins end) as [UEFA Super Cup],  
SUM(case when Competition='FIFA Club World Cup' then Wins end) as [UEFA Super Cup]

From dbo.Table
Group by [First Name], [Last Name], Club

You cannot leave un-aggregated items in a group by query in SQL Server, even though you know that there would be at most one non-null entry for each item.

Upvotes: 2

Related Questions