mysql - SQL Query: Combine multiple rows [using a CASE WHEN x IN (1,2,3...)] to one row -

i want combine rows of same media_type value 1 row.

here query (slightly modified keep private info private):

select  case     when media_type in ('1','3') 'a'     when media_type in ('2','4') 'b'     when media_type in ('5','21','22') 'c'     when media_type in ('7','8','23') 'd'     when media_type in ('9') 'e' end media_type, sum(imp) imps, sum(seller_revenue) seller_revenue, sum(seller_revenue) / sum(imp)  cpm      table       dd = '2016-01-19'  group     media_type  order     seller_revenue desc  limit 100; 

current output (data changed privacy):

media_type  imps    seller_revenue  cpm   2681524581  4636356  1.729 b   18152099106 22345234 1.231   113355218.6 635356   5.605   83881452.47 235623   2.809   27994649.23 235435   8.41 c   4924414.093 63456    12.886 d   522212.1487 3456     6.618 c   569451.3099 3456     6.069 b   821059.4315 2542     3.096 b   220102.1505 409.39   1.86 d   103231.5978 345      3.342   88757.39645 345      3.887 e   300261.0966 345      1.149   213622.291  345      1.615 b   118701.5504 245      2.064 d   211678.8321 145      0.685 b   57798.16514 63       1.09 e   47619.04762 56       1.176 c   44191.91919 35       0.792 b   140186.9159 15       0.107   4288.777698 6        1.399  

desired output (sorting not important):

media_type  seller_revenue  imps    cpm_calc b    22,348,508      18,153,456,955      1.23     5,743,466       2,907,062,569       1.98  c    66,947          5,538,057           12.09  d    3,946           837,123             4.71  e    401             347,880             1.15  grand total 28163268.39 21067242584 1.33682746 

thank you!

surprised haven't found asked before hour of searching, wonder if it's not issue?

you didn't mention sql vendor you're using...

if you're using oracle, think use wm_concat

for mysql, use group_concat

(here's example: https://www.percona.com/blog/2013/10/22/the-power-of-mysqls-group_concat/ )

and sql-server has wonky workarounds achieve same effect.