Selecting data from MYSQL table based on rows

Question

I have the following sql table:

MONTH   CODE    DESCR    QTY    PRICE
201401  3641    HO       29      3829
201401  3641    IL       2      2
201401  3641    Office   34     43576
201401  3641    Other    38     424
201401  3641503 HO      2904    13584
201401  3641503 IL      2045    2833
201401  3641503 Office  106     237
201401  3641503 Other   79      129
201401  364     HO      37      182
201401  364     IL      8        9
201401  364     Office  750     1317
201401  364     Other   5        6

from this table i want to create another table which looks like this:

  MONTH   CODE      HO_qty    IL+Office_QTY   Other_QTY    HO_Price  IL+Office_Price   Other_Price
 201401  3641       29           36(34+2)      38          3829        43578(43576+2)    424
 201401  3641503    2904         2151          79          13584       3070              129
 201401  364        37           758            5          182         1326               6

The SQl fiddle is here: http://sqlfiddle.com/#!2/cf745df/1

Any pointers on approach?

Chris du Preez · Accepted Answer · 2015-02-11 01:18:57Z

2

Think this is probably what you are looking for:

Select 
 MONTH, CODE,
 sum(case when descr in ('Office','IL') then QTY end) as `IL+Office_QTY`,
 sum(case when descr = 'HO' then QTY end) as `HO_qty`,
 sum(case when descr = 'Other' then QTY end) as `Other_QTY`,

 sum(case when descr in ('Office','IL') then PRICE end) as `IL+Office_Price`,
 sum(case when descr = 'HO' then PRICE end) as `HO_Price`,
 sum(case when descr = 'Other' then PRICE end) as `Other_Price`
from Table1
group by MONTH, CODE

answered Feb 11, 2015 at 1:18

Chris du Preez

5543 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Morpheus Over a year ago

Thanks. For a moment, i forgot about case statements.

Collectives™ on Stack Overflow

Selecting data from MYSQL table based on rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related