Select a subquery with a temp table. 1

[davism]

Hi all,

I'm not getting this one entirely.

I have a script that creates a temporary table and inserts into it from another select.

Then I select everything from the temporary table. I have an aggregate function (SUM) included which is why I am selecting from the temp table at the end.

I have 2 rows that need to be consolidated but the SUM is not working because there are 2 other fields that are different. So, I was thinking that I can have the temporary table combine everything and then get the 2 fields in the final output.

I have data something like this:

a b c d e f g
h i j k l m n

Everthing is essentially the same on the 2 rows except for e, f, g is different than l, m, n

Ideally what I should have is like:

a b (c+j) (d+k) (e+l) m n

The m and n columns are because they are not null. Whereas "f" and "g" are.

How can something like this be achieved?

Any information is greatly appreciated.

Thanks

Columns b c d are summed each. Columns f and g are the only 2 different items on the row.

 

[PDreyer]

You don't need a temp table

select a, b, sum(c), sum(d), sum(e), min(f), min(g)
from /* Test data */              (select 
'x','y',1 ,10, 100, null, null     union all select 
'x','y',2 ,20, 200, 'z' , 9       )f(a,b,c,d,e,f,g)
group by a, b

 

[davism]

Much appeciated as that will solve a good portion as well.

However, when I got home last night, I thought about it and the solution really hit me. UPDATE.

I needed to create the temporary table and that I did. I updated the temporary table to provide the field information.

Works like a charm.

Thanks though and much appreciated.