select sum(select sum(...))

Question

Post reply

select sum(select sum(...))

moammermohsen

SSC Journeyman

Points: 91
More actions
March 30, 2014 at 5:23 pm

#396501

how to do this query
sum of sum
i want to Aggregate the results of a sub query which aggregate items
if i has a table with fields field_1,field_2,field_3 and my sub query aggregates sum(field_1 * field_2 / field_3)
for every product as cost_field
this gives a column of cost_field for every product
and i want the aggregation the column of cost_field
like
select sum(select sum(sum(field_1 * field_2 / field_3) ) and obtain only on value of the cost of all products
i used cost as an example

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

mickyT SSChampion Points: 10360 More actions · Answer 1

Hi

I am failing to see why you would need to do a sum of a sum. Surely only a single sum is required?

As an example, the two following queries have the same result. Have I missed something?

SELECT * INTO#testdata

FROM (VALUES

(1,1.0,10.0,2.0),

(1,2.0,10.0,2.0),

(1,3.0,10.0,3.0),

(2,1.5,10.0,1.0),

(2,1.3,10.0,2.0),

(2,1.7,10.0,4.0),

(3,3.0,10.0,2.0),

(3,3.0,10.0,3.0),

(3,5.0,5.0,1.0)

) td(productid, f1, f2, f3);

go

WITH preagg AS (

SELECT productid, SUM(f1 * f2 / f3) c

FROM #testdata

GROUP BY productid

)

SELECT SUM(c) c FROM preagg;

SELECT SUM(f1 * f2 / f3) c

FROM #testdata;

go

drop table #testdata;

c

---------------------------------------

100.750000

(1 row(s) affected)

c

---------------------------------------

100.750000

(1 row(s) affected)

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 2

moammermohsen (3/30/2014)
how to do this query
sum of sum
i want to Aggregate the results of a sub query which aggregate items
if i has a table with fields field_1,field_2,field_3 and my sub query aggregates sum(field_1 * field_2 / field_3)
for every product as cost_field
this gives a column of cost_field for every product
and i want the aggregation the column of cost_field
like
select sum(select sum(sum(field_1 * field_2 / field_3) ) and obtain only on value of the cost of all products
i used cost as an example

Welcome to SSC. Take a look at the article located at the first link under "Helpful Links" in my signature line below. It will show you how to post code related questions the best way to get the best answers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)