Inserting a column twice

[thrybergh]

Hello.

I have a table which contains (amongst others) a price and two category codes. (There are two category codes for each item as the categorisation varies depending on the type of the project.)

We run a daily extract which sums the item rows by the category. The extract table looks like this:

ProjectCode
Category1 (if below 1000)
Category2 (if below 10000)
SumValue

Where the SumValue column holds the total value of the items in that ProjectCode for Category1.


I would like to modify the extract to make it look like this:

ProjectCode
Category1
Category2
SumValue1
SumValue2

This is so I can have different SumValue columns depending on project type. I have used a DECODE statement to produce the Category1 and Category2 columns OK (which looks at the value to decide if it is below 1000 or 10000), but I basically need to extract the SumValue column twice in the same SELECT statement.

Is it possible to SUM looking at Category1 and insert the summed amount, then re-SUM by Category2 and insert the summed amount, or would I have to have two separate passes in the extract routine?

Thanks for any pointers.