sum nested if statement - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

sum nested if statement

Author

Topic

dean.c.cummins
Starting Member

6 Posts

Posted - 2011-01-11 : 08:36:41

im having problems using an aggregate function on a nested select statement. please find my code below

SELECT Year(t_odat) as Year, SUM(t_amta) as Spend,
(
SELECT SUM(t_amta)
FROM ttipcs360100
WHERE t_cprj = cast(t_orno as char(6))
) as Spend2
FROM ttdsls041100 INNER JOIN ttiitm001100 ON ttdsls041100.[t_item] = ttiitm001100.[t_item]
WHERE t_orno >= 800000 AND t_orno <= 879000 AND t_cuno = ' 0004'
GROUP BY Year(t_odat), t_orno
ORDER BY Year(t_odat)

this produces

Year Spend Spend2
1996 122 13.2
1997 132.46 13.2
1999 96.96 12.6
2000 101.34 13.63
2001 1217.49 609.17
2004 1232 431.88
2004 2614.52 1076.5632
2004 0 498.87
2006 929.87 1017.5
2008 6515.6 2651.78
2009 474.15 147.13
2010 8364.64 4798.12
2010 2610.15 989.56
2010 556.15 361.09
2010 5296 2633.75
2010 712.66 448.99
2010 1064.83 638.65
2010 849.99 504.74
2010 119.06 62.56
2010 640.72 410.83
2010 6276.02 3589.22
2010 1045.06 645.83
2010 1381.11 531.47
2010 1426.99 775.06

however i want sum up all the columns of spends, so i have a distinct year.

so i need to sum my nested select statement. but i keep on getting an error.

cheers x

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-11 : 09:12:08

Remove the red part below:


SELECT Year(t_odat) as Year, SUM(t_amta) as Spend,
		(
		SELECT SUM(t_amta)
		FROM ttipcs360100
		WHERE t_cprj = cast(t_orno as char(6))
) as Spend2
FROM ttdsls041100 INNER JOIN ttiitm001100 
		ON ttdsls041100.[t_item] = ttiitm001100.[t_item]
WHERE t_orno >= 800000 AND t_orno <= 879000 AND t_cuno = ' 0004'
GROUP BY Year(t_odat), t_orno
ORDER BY Year(t_odat)

You are grouping by t_orno which is likely causing your issue.

Poor planning on your part does not constitute an emergency on my part.

dean.c.cummins
Starting Member

6 Posts

Posted - 2011-01-11 : 10:05:01

if i remove that i get an error cos t_orno is in the nested select statement x

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources