0

Weird question... I know. So let me explain.

Here is a my text table

    quota     |    metric_id
     100      |        1
     20       |        2
     100      |        15
     50       |        1
     30       |        2
     20       |        15
     50       |        1
     200      |        2
     10       |        15

I want a SELECT statement that would return the following

    id_1    |     id_2     |      id_15 
    200     |     250      |      130

Here is what the data is:

Column `id_1` is sum of the 3 rows where `metric_id=1`
Column `id_2` is sum of the 3 rows where `metric_id=2`
Column `id_15` is sum of the 3 rows where `metric_id=15`

Any help?

raina77ow
  • 103,633
  • 15
  • 192
  • 229
codeNinja
  • 1,442
  • 3
  • 25
  • 61
  • Convert `Column 'id_1' is sum of the 3 rows where 'metric_id=1'` into SQL and you're good to go. – Shomz Apr 03 '14 at 21:48
  • possible duplicate of [How to pivot a MySQL entity-attribute-value schema](http://stackoverflow.com/questions/649802/how-to-pivot-a-mysql-entity-attribute-value-schema) – Vatev Apr 03 '14 at 21:48
  • Is `metric_id` static? Just those 3 values? – juergen d Apr 03 '14 at 21:48
  • Consider handling issues of data display at the application level/presentation layer (e.g. a simple PHP loop acting upon an ordered array) – Strawberry Apr 03 '14 at 21:54

2 Answers2

0

Try this

select SUM(case when metric_id = 1 then quota END) id_1
       SUM(case when metric_id = 2 then quota END) id_2
       SUM(case when metric_id = 15 then quota END) id_15
FROM Table1
rs.
  • 26,707
  • 12
  • 68
  • 90
0

One possible approach:

SELECT SUM(quota * (metric_id = 1)) AS id_1,
       SUM(quota * (metric_id = 2)) AS id_2,
       SUM(quota * (metric_id = 15)) AS id_15
FROM Table1

Demo. It's quite simple: metric_id = N is 1 for the rows having that attribute equal to N, and 0 for all the other rows. Multiplying quota by 1 is quota, by 0 - 0, and the rest is an easy task for SUM() function.

raina77ow
  • 103,633
  • 15
  • 192
  • 229