Combining Rows and Taking Averages

Question

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

Robert · Accepted Answer · 2013-07-23T19:59:04.780

3

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')

edited Jul 23 '13 at 19:59

answered Jul 23 '13 at 19:52

Robert

25,425
8
67
81

Need to UNION the record for `C` or refactor to put the `IN` in a GROUP BY rather than the WHERE clause. – MatBailie Jul 23 '13 at 19:54
OP wants **C** line to remain in the result. – PM 77-1 Jul 23 '13 at 19:55
@MatBailie Yes indeed! – Robert Jul 23 '13 at 19:58
@user2573999 I'm glad I could help you. Don't forget to accept the answer to avoid low accepted rate an close the question. You can also vote for the answer ;) – Robert Jul 23 '13 at 20:07

score 2 · Answer 2 · answered Jul 23 '13 at 19:54

2

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

answered Jul 23 '13 at 19:54

Ian Kenney

6,376
1
25
44

Couldn't you `GROUP BY sect`? – Jeromy French Jul 24 '13 at 01:48
Ah hah. Thanks--I've been using PostgreSQL for the past year, so my MSSQL learnin's are starting to fade. So `GROUP BY 1` (column poisition) would also be possible, but prone to problems down the road if the query is changed and the returned columns are moved around... – Jeromy French Jul 24 '13 at 13:32

Combining Rows and Taking Averages

2 Answers2