pulling sum() from subquery - 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 2005 Forums

Transact-SQL (2005)

pulling sum() from subquery

Author

Topic

bogey
Posting Yak Master

166 Posts

Posted - 2007-09-27 : 16:11:22

Anyone have an example of pulling a sum from a subquery.

I have "1" table that has a parent, ogranization, grant_number, award_amount, grant_type.

I need to sum all parents by award_amount for a specific year but I also need to gather the previous years sum fro a specific grant_type.

So I need this years sum and then I need to go back a year (using a subquery) and get another sum.

Thanks.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-09-27 : 16:21:43

declare @t table (mydate datetime, mygroup int, mymoney money)
insert into @t values ('01-01/2006', 1, 100.00)
insert into @t values ('02-01/2006', 2, 200.00)
insert into @t values ('03-01/2006', 2, 300.00)
insert into @t values ('04-01/2007', 1, 50.00)
insert into @t values ('05-01/2007', 2, 70.00)

select
mygroup,
sum(mymoney),
(select sum(mymoney) from @t where s.mygroup = mygroup and Year(mydate) = (Year(GetDate())-1)) as LastYear
from @t s where Year(myDate) = Year(GetDate())
group by mygroup

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-27 : 18:13:01

quote:
Originally posted by bogey

Anyone have an example of pulling a sum from a subquery.

I have "1" table that has a parent, ogranization, grant_number, award_amount, grant_type.

I need to sum all parents by award_amount for a specific year but I also need to gather the previous years sum fro a specific grant_type.

So I need this years sum and then I need to go back a year (using a subquery) and get another sum.

Thanks.

Post your table DDL, sample data and required result

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 03:00:22

This will work for any case.

-- Prepare sample data
DECLARE @Sample TABLE (MyDate DATETIME, MyGroup INT, MyMoney MONEY)

INSERT	@Sample VALUES ('20060101', 1, 100.00)
INSERT	@Sample VALUES ('20060102', 2, 200.00)
INSERT	@Sample VALUES ('20060103', 2, 300.00)
INSERT	@Sample VALUES ('20070104', 1,  50.00)
INSERT	@Sample VALUES ('20070105', 2,  70.00)

-- Show the expected output
SELECT		MyGroup,
		SUM(CurrMoney) AS CurrMoney,
		SUM(PrevMoney) AS PrevMoney
FROM		(
			SELECT		MyGroup,
					MyMoney AS CurrMoney,
					0 AS PrevMoney
			FROM		@Sample
			WHERE		MyDate >= DATEADD(YEAR, DATEDIFF(YEAR, '19000101', CURRENT_TIMESTAMP), '19000101')
					AND MyDate < DATEADD(YEAR, DATEDIFF(YEAR, '18991231', CURRENT_TIMESTAMP), '19000101')

			UNION ALL

			SELECT		MyGroup,
					0 AS CurrMoney,
					MyMoney AS PrevMoney
			FROM		@Sample
			WHERE		MyDate >= DATEADD(YEAR, DATEDIFF(YEAR, '19010101', CURRENT_TIMESTAMP), '19000101')
					AND MyDate < DATEADD(YEAR, DATEDIFF(YEAR, '19000101', CURRENT_TIMESTAMP), '19000101')
		) AS h
GROUP BY	MyGroup
ORDER BY	MyGroup

E 12°55'05.25"
N 56°04'39.16"

bogey
Posting Yak Master

166 Posts

Posted - 2007-09-28 : 10:52:11

quote:
Originally posted by khtan

quote:
Originally posted by bogey

Anyone have an example of pulling a sum from a subquery.

I have "1" table that has a parent, ogranization, grant_number, award_amount, grant_type.

I need to sum all parents by award_amount for a specific year but I also need to gather the previous years sum fro a specific grant_type.

So I need this years sum and then I need to go back a year (using a subquery) and get another sum.

Thanks.

Post your table DDL, sample data and required result

KH
[spoiler]Time is always against us[/spoiler]

USE [NIH_FUNDING]
GO
/****** Object: Table [dbo].[NIHAwards] Script Date: 09/28/2007 10:37:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[NIHAwards](
[Organization Name] [nvarchar](500) NULL,
[Grant Number] [nvarchar](500) NULL,
[Award] [money] NULL,
[Parent] [varchar](50) NULL,
[GrantYear] [char](4) NULL,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

insert into [NIHAwards]([Organization Name],[Grant Number],[Award],[Parent],[GrantYear]) values (N'A',N'R21',2500.0000,'A','2006')
insert into [NIHAwards]([Organization Name],[Grant Number],[Award],[Parent],[GrantYear]) values (N'A',N'R21',3200.0000,'A','2006')
insert into [NIHAwards]([Organization Name],[Grant Number],[Award],[Parent],[GrantYear]) values (N'A',N'NO1',400.0000,'A','2005')
insert into [NIHAwards]([Organization Name],[Grant Number],[Award],[Parent],[GrantYear]) values (N'A',N'NO1',200.0000,'A','2005')

GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 10:55:47

[code]SELECT [Organization Name],
SUM(CurrMoney) AS CurrMoney,
SUM(PrevMoney) AS PrevMoney
FROM (
SELECT [Organization Name],
Award AS CurrMoney,
0 AS PrevMoney
FROM NIHAwards
WHERE GrantYear = 2007

UNION ALL

SELECT [Organization Name],
0 AS CurrMoney,
Award AS PrevMoney
FROM NIHAwards
WHERE GrantYear = 2006
) AS h
GROUP BY [Organization Name]
ORDER BY [Organization Name][/code]

E 12°55'05.25"
N 56°04'39.16"

bogey
Posting Yak Master

166 Posts

Posted - 2007-10-04 : 10:48:27

Thanks Peso. I was finally able to get back to this today and its seems to work great.

Again, thanks

quote:

Originally posted by Peso

SELECT		[Organization Name],
		SUM(CurrMoney) AS CurrMoney,
		SUM(PrevMoney) AS PrevMoney
FROM		(
			SELECT		[Organization Name],
					Award AS CurrMoney,
					0 AS PrevMoney
			FROM		NIHAwards
			WHERE		GrantYear = 2007

			UNION ALL

			SELECT		[Organization Name],
					0 AS CurrMoney,
					Award AS PrevMoney
			FROM		NIHAwards
			WHERE		GrantYear = 2006
		) AS h
GROUP BY	[Organization Name]
ORDER BY	[Organization Name]

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-04 : 11:25:31

this may or may not be more efficient, it is shorter:


select 
  [Organization Name], 
  sum(case when GrantYear='2007' then Award else 0 end) as CurrMoney,
  sum(case when GrantYear='2006' then Award else 0 end) as PrevMoney
from 
  NIHAwards
where 
  GrantYear between 2006 and 2007
group by 
  [Organization Name]

ALso, if GrantYear is a char(4) column (why???), then use '2007' and not 2007 to avoid the need for implicit conversions.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources