Help needed with nested aggregate

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help needed with nested aggregate

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2010-02-12 : 06:09:52

I need to calculate a weighted Total of payment delay, where each figure contributes to the Total by its proportion of the Total Amount of Invoice.

9'500€ - 40 days after duedate
500€ - 10 days after duedate
-------
10'000€ - 40 d (9'500/10'000)+ 10 d (500/10'000) = 38,5 d

This requires a nested aggregate query, and I can't get a workaround... any ideas? The below query does not work...

SELECT SUM(DATEDIFF(day, dbo.CUSTTRANS.DUEDATE, dbo.CUSTTRANS.CLOSED) * dbo.CUSTTRANS.SETTLEAMOUNTCUR / SUM(dbo.CUSTTRANS.SETTLEAMOUNTCUR)) AS DIFF
FROM dbo.CUSTTRANS INNER JOIN dbo.CUSTTABLE ON dbo.CUSTTRANS.ACCOUNTNUM = dbo.CUSTTABLE.ACCOUNTNUM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 07:39:36

do you mean this?


SELECT SUM(DATEDIFF(day, dbo.CUSTTRANS.DUEDATE, dbo.CUSTTRANS.CLOSED) * (dbo.CUSTTRANS.SETTLEAMOUNTCUR *1.0/ (SELECT SUM(dbo.CUSTTRANS.SETTLEAMOUNTCUR) FROM dbo.CUSTTRANS))) AS DIFF
FROM dbo.CUSTTRANS INNER JOIN dbo.CUSTTABLE ON dbo.CUSTTRANS.ACCOUNTNUM = dbo.CUSTTABLE.ACCOUNTNUM

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-02-12 : 07:53:01

Maybe this
DECLARE @Table TABLE (Amount int,PastDue int)
INSERT INTO @Table
SELECT 9500 ,40 UNION
SELECT 500,10

SELECT SUM(amt)
FROM
(
select [amt] = (pastdue*Amount*1.0E/SUM(Amount) OVER() )
from @Table
) a

Jim

Everyday I learn something that somebody else already knew

barnabeck
Posting Yak Master

236 Posts

Posted - 2010-02-12 : 08:13:44

visakh16: Yes this is the same query... and the same result, as it throws the error "cannot perform an aggregate function on an expression containing an aggregate..." As I know this sort of expression works on two independent queries, but these are nested

jimf: The result is correct; I will try to adapt this to my table columns and see what I can get... on Monday.

Have a nice weekend

barnabeck
Posting Yak Master

236 Posts

Posted - 2010-02-15 : 07:02:54

Jim,

I adapted my query to your expression, but as I introduce the group section, the results get messed up:
The following query calculates the weighted sum for each month -almost-correctly, but instead of dividing by the month_total it divides by the anual_total.

SELECT SUM(amt) AS Month_Average

FROM
(
select [amt] = ( DATEDIFF(day, dbo.CUSTTRANS.DUEDATE, dbo.CUSTTRANS.CLOSED)*SETTLEAMOUNTCUR*1.0E/SUM(SETTLEAMOUNTCUR) OVER() ), DATEPART(month, dbo.CUSTTRANS.TRANSDATE) AS Month
FROM dbo.CUSTTRANS INNER JOIN dbo.CUSTTABLE ON dbo.CUSTTRANS.ACCOUNTNUM = dbo.CUSTTABLE.ACCOUNTNUM
WHERE (DATEPART(year, dbo.CUSTTRANS.TRANSDATE) = '2009')
) a

Group By Month

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 08:00:03

Would be better if you can explain discrepancy with some table data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

barnabeck
Posting Yak Master

236 Posts

Posted - 2010-02-16 : 05:44:18

Month Amount Days
1 206.340000000000 7
2 104.930000000000 39
4 206.340000000000 25
4 1062.100000000000 11
4 1062.100000000000 11

The result should be:
1 206.340000000000 7
2 104.930000000000 39
4 2330.540000000000 12.24 (206.34*25+1062.10*11+1062.10*11)/(206.34+1062.10+1062.10)

while the above expression would give -just for the last column:
0.55
1.55
10.79

NOW I found the solution, investigating on the OVER clause: What in my understanding was the Group Clause inside the brackets, is placed as a Partition expression... so its:

SELECT SUM(amt) AS Month_Average

FROM
(
select [amt] = ( DATEDIFF(day, dbo.CUSTTRANS.DUEDATE, dbo.CUSTTRANS.CLOSED)*dbo.CUSTTRANS.SETTLEAMOUNTCUR*1.0E/SUM(dbo.CUSTTRANS.SETTLEAMOUNTCUR) OVER(PARTITION BY DATEPART(month, dbo.CUSTTRANS.TRANSDATE))), DATEPART(month, dbo.CUSTTRANS.TRANSDATE) AS Month
FROM dbo.CUSTTRANS INNER JOIN dbo.CUSTTABLE ON dbo.CUSTTRANS.ACCOUNTNUM = dbo.CUSTTABLE.ACCOUNTNUM
WHERE (DATEPART(year, dbo.CUSTTRANS.TRANSDATE) = '2009')
) a
Group by Month

Cheers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 06:04:00

[code]SELECT Month,SUM(Amount),SUM(Amount* Days)/SUM(Amount)
FROM Table
GROUP BY Month
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

barnabeck
Posting Yak Master

236 Posts

Posted - 2010-02-16 : 06:33:42

Cool, now I don't understand why I haven't seen it in the first place, actually it is not a case of nested agregate functions as the expression

Sum ( Days * Amount/Sum(Amount)) is identical to Sum (Days * Amount)/Sum (Amount)

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources