Need help combining these two queries

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)

Need help combining these two queries

Author

Topic

terryagi
Starting Member

3 Posts

Posted - 2009-04-23 : 11:45:03

I am trying to get the data indicated by the first query. However as indicated by the second query, I am also wanting to return the sum of the budget amounts where the ids are the same and primary equals one or four.

Basically, I'm trying to combine the two queries below, but I keep running into issues with the SUM. The data looks like this:


cgebioi mnt_oi     id        budgetamt  primary
2       67591   Test Parent   1000         1
11      67591   blahEDIT      1000         1
13      67591   Test Parent   5000         4

And here are the queries:


SELECT [cgebioi], [mnt_oi], [id], [budgetamt], [primary]
FROM ParentBudgetEntity
WHERE [mnt_oi] = @MaintenanceID
  AND [primary] = 1;


SELECT SUM([budgetamt]) AS CurrentBudget
FROM ChildBudgetEntity
WHERE [mnt_oi] = @MaintenanceID
  AND [primary] IN (1, 4)
GROUP BY [id];

This is the result set that I am expecting:


cgebioi mnt_oi     id        budgetamt  primary  CurrentBudget
2       67591   Test Parent   1000         1         6000
11      67591   blahEDIT      1000         1         1000

Any suggestions?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-23 : 11:52:50

may be this?

SELECT [mnt_oi], [id], sum(budgetamt)
FROM ParentBudgetEntity
WHERE [mnt_oi] = @MaintenanceID
AND [primary] = 1;
group by [mnt_oi],[id]

You have two different cgebioi's and primary's for the same [id] and you haven't mentioned which one you want to select..hence I have removed from the SELECT.

terryagi
Starting Member

3 Posts

Posted - 2009-04-23 : 12:28:49

The [id] field is misleading. [cgebioi] is the unique identifier for each record. No other fields are unique.

I need all of the data ([cgebioi], [mnt_oi], [id], [budgetamt], [primary]) for the primary==1 records, but I also need the sum of the [budgetamt]s where the [id]s match and [primary] IN (1,4).

Here is a different explanation:
The first ParentBudget entered into the database (according to [id]) will be given primary=1, indicating that the entry is the initial budget. Any subsequent entries matching the previously entered ParentBudget's [id] will be given primary=4, indicating that the entry is a budget change. The CurrentBudget is the sum of all of the entries with the same [id].

BTW, I did not set up this database. ;)

notmyrealname

98 Posts

Posted - 2009-04-23 : 13:04:23

quote:

This is the result set that I am expecting:


cgebioi mnt_oi     id        budgetamt  primary  CurrentBudget
2       67591   Test Parent   1000         1         6000
11      67591   blahEDIT      1000         1         1000

Any suggestions?

This gives what you show above. I am not sure if i am missing something.

SELECT MIN(cgebioi) AS cgebioi, mnt_oi, id, MIN(budgetamt) AS budgetamt, MIN([primary]) AS [primary], SUM(budgetamt) AS CurrentBudget
FROM ParentBudgetEntity
GROUP BY mnt_oi, id
ORDER BY cgebioi

Hope it helps.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-23 : 13:34:45

Ok..Try this. I think it'll give what you need

declare @t table (cgebioi int, mnt_oi int, id varchar(100), budgetamt int,[primary] int)
insert @t
select 2,67591,'Test Parent',1000,1 union all
select 11,67591,'blashEDIT',1000,1 union all
select 13,67591,'Test Parent',5000,4

select t.cgebioi,t.mnt_oi,t.id,t.budgetamt,t.[primary],r.currentbudget
from @t t join
(select id,sum(budgetamt) as currentbudget from @t where [primary] in (1,4) group by id) r
on t.id = r.id
and t.[primary] = 1
order by t.cgebioi

Result
------
2 67591 Test Parent 1000 1 6000
11 67591 blashEDIT 1000 1 1000

terryagi
Starting Member

3 Posts

Posted - 2009-04-23 : 14:13:22

Both of these solutions worked. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources