-2

I have a table with Lots of cost columns for each Key TableA

SK1 SK2 Col1 Col2 Col3..... Col50 Flg(Y/N)

1    2    10    20   30 ...... 500  Y
1    2    10    20   30 ...... 500  N
2    2    10    20   30 ...... 500  N

I need to aggregate(sum) of all values and then check if there are any values with Y then add them to new tableB. Here table A record combination (1,2) for (sk1,sk2) should be returned.

The i have written query is to select lisr of all cols and add as group by. We have lots of data so this query is taking too long to run. Any chance to relook into this and do so that it can become faster.

select Sk1, Sk2, nvl(sum(col3),0), nvl(sum(col4))0, ..... nvl(sum(col50)) from table A group by Sk1, Sk2

Iam using this as part of large query where in many other calculations are performed on top of this.

waiting
  • 45
  • 6

3 Answers3

0

Working out whether any of a grouped set of records contains a 'Y' would be as simple as ...

select   ...
from     ...
group by ...
having   max(flg) = 'Y'
David Aldridge
  • 51,479
  • 8
  • 68
  • 96
  • this would give me only those values which are having Y, i have to compute sum over all and then display or takeup only those that are Y – waiting May 25 '16 at 05:32
  • The having clause applies a filter to the query result after the aggregation is applied, not to the input rows. So it will return all the aggregated rows for which the maximum value of flg for the source rows is "Y". – David Aldridge May 25 '16 at 14:02
0

For now i have created a temporary table and have loaded all the data into it.

waiting
  • 45
  • 6
-1

If you are using this as part of large query, did you try WITH option?

It could be like this

WITH SUM_DATA AS (select col1, col2, nvl(sum(col3),0), nvl(sum(col4))0, ..... nvl(sum(col50)) from table A group by col1, col2)

SELECT xyz 
FROM abc, sum_data
WHERE abc.join_col = sum_data.join_col

More help here

Srini V
  • 11,045
  • 14
  • 66
  • 89
apb
  • 1