Modify table content with a sql query

Question

I have a db table like this:

------------------------------------
--Col1---Col2---Col3---Col4---Col5--
|  A   |  B   |   C  |  D   |  -10  | 
|  A   |  B   |   C  |  D   |  -10  |
|  A   |  B   |   C  |  D   |  30   |
|  D   |  F   |   C  |  D   |  400  |
|  X   |  F   |   C  |  D   |  250  |
|  A   |  B   |   C  |  D   |  75   |

I want to transform this table into this form:

------------------------------------
--Col1---Col2---Col3---Col4---Col5--
|  A   |  B   |   C  |  D   |  85   | 
|  D   |  F   |   C  |  D   |  400  |
|  X   |  F   |   C  |  D   |  250  |

As you see Col5 value is the sum of repeated Col1,Col2,Col3,Col4 values. How can I do that with a sql query or pl/sql script?

use GROUP BY and SUM should do the trick. But from your info it is unclear if Col1-4 are changing and if so how — luksch

Raging Bull Raging Bull · Accepted Answer · 2014-03-29T15:41:11

Try this:

SELECT Col1,Col2,Col3,Col4,SUM(Col5) as Col5
FROM TableName
GROUP BY Col1,Col2,Col3,Col4

Since SUM is an aggregate function, you need to GROUP BY all other selected values.

Result:

COL1    COL2    COL3    COL4    COL5
A       B       C       D       85
D       F       C       D       400
X       F       C       D       250

See result in SQL Fiddle.

Modify table content with a sql query

1 Answers