CODEWITHSUNDEEP
sqlsql-serversql-server-2008
euge1220
euge1220

Reputation: 235

Combining Rows and Taking Averages

I have a table that looks like 

#Sector  max1   avg1   max2  avg2  numb
C         133    14     45    3     27
N         174     9     77    3     18
M         63      3     28    1     16

I would like to join rows N and M together call it X and take the max value of max1 and max2 while taking the avg of avg1, avg2, and numb in their respective columns to return

#Sector  max1   avg1   max2  avg2  numb
C         133    14     45    3     27
X         174     6     77    2     17

Upvotes: 1

Views: 67

Answers (2)

Robert
Robert

Reputation: 25753

Try this way:

select sector, max1,avg1,max2,avg2,numb
from tab
where sector not in ('M','N')
union all
select 'X' as sector, max(max1),avg(avg1),max(max2),avg(avg2),avg(numb)
from tab
where sector in ('M','N')

Upvotes: 3

Ian Kenney
Ian Kenney

Reputation: 6426

something like: 

select 
  case when sector in ('N','M') then 'X' else sector end sect,
  max(max1) max1,
  avg(avg1) avg1,
  max(max2) max2,
  avg(avg2) avg2,
  avg(numb) numb
from tabname 
group by 
  case when sector in ('N','M') then 'X' else sector end

Upvotes: 2

Related Questions