Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

So I'm wanting to summarize the balance of the accounts in my business application. My (simplified for our purposes) data model looks like:

--Main account table that holds the single accounts
create table account (
id int primary key,
descr varchar(50))

--This table holds the budgeted amount for the account.  This table also holds the
--'actual' adopted amount.  The difference is that budgeted is what they thought it
--should be, while adopted is what the board accepted.  They can be different
create table account_amount (
id int primary key,
acct_id int foreign key references account (id),
amount money,
type tinyint)

--Each monitary action against an account is contained here.  This is the transactional
--table
create table account_journal (
id int primary key,
acct_id int foreign key references account (id),
amount money)

I want to create a view to summarize the balance for me, so I've got something like this:

create view account_balance as
select a.id, isnull(aaBudgeted.amount,0) as budgeted, isnull(aaAlloted.amount,0) as alloted
, sum(isnull(aj.amount,0)) as journaled_activity,
, isnull(aaAlloted.amount,0) + sum(isnull(aj.amount,0)) as balance
from account a left join acct_amount aaBudgeted on a.id = aaBudgeted.acct_id
and aaBudgeted.type = 2
left join acct_amount aaAlloted on a.id = aaAlloted.acct_id
and aaAlloted.type = 1 and aaAlloted.is_current = 1
left join acct_journal aj on aj.acct_id = a.id
where a.fiscal_year = 2012
group by a.id, aaBudgeted.amount, aaAlloted.amount

My question has to with the:

isnull(aaAlloted.amount,0) + sum(isnull(aj.amount,0)) as balance

line above. Do I want to be referencing an aggregate column twice like this? Is SQL Server 2k (don't get me started) smart enough to NOT sum the values twice? What are my options?

share|improve this question
    
Not sure if you're willing to re-structure the query, and I haven't really parsed the query myself, but if you want to be safe you can always use subqueries. I typically do that not just to avoid any potential optimizer ambiguities but also because I'm lazy. –  Aaron Bertrand Sep 9 '11 at 18:38
    
Definitely an option. Let's say though that I wanted to make this an indexed view, then that would prevent the sub querey and the summing of nullable fields. –  Nick DeVore Sep 9 '11 at 18:48
    
You've already eliminated the possibility of it being an indexed view - SUM(), left joins, etc. sqlteam.com/article/indexed-views-in-sql-server-2000 –  Aaron Bertrand Sep 9 '11 at 18:51

1 Answer 1

up vote 2 down vote accepted

The aggregate will only be calculated once, so you're ok as you are. That said, I tend to use a subquery (as per @Aaron's comment) but purely for readability.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.