Sum calculation - 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 calculation

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-10-02 : 18:10:45

A =
(select sum(loss_amt)
from claim c
Inner Join location_xref x on c.ref_location = x.location_key
Left Join Loss l on c.claim_key = l.loss_claim

where x.ref_loc1 = A56478 and
c.clm_status in ( 'R' , 'O')
loss_type = 'R'
group by clm_nbr,clm_clmt,clm_incdate,clm_status
)

B=
(Select sum(loss_amt)
from claim c
Inner Join location_xref x on c.ref_location = x.location_key
Left Join Loss l on c.claim_key = l.loss_claim
where x.ref_loc1 = A56478 and
c.clm_status in ( 'R' , 'O')
loss_type = 'Y'
group by clm_nbr,clm_clmt,clm_incdate,clm_status
)

I would like to write a report that shows a C value(which is the result of A - B vlaue) .

Looks something like:

Select clm_nbr,clm_clmt,convert(char(15),clm_incdate, 111),clm_status, C ( which is the result of A - B)
from claim c
Inner Join location_xref x on c.ref_location = x.location_key
Left Join Loss l on c.claim_key = l.loss_claim

where x.ref_loc1 = 5489 and
c.clm_status in ( 'R' , 'O')

group by clm_nbr,clm_clmt,clm_incdate,clm_status

Any help would be apprecited..

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-10-02 : 22:06:28

my poor brain

Any chance of some DDL and perhaps a brief comment about what "A", "B" and "C" are?

Sorry to be a whinger...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-10-02 : 22:16:45

There are just abbreviations for sum (loss_amt).
A = sum(loss amt) where loss_type = 'R'
B= Sum(loss_amt) where loss_type = 'Y'

And, C is a colculated column:
C = A - B

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-03 : 07:31:46

You cant reference the derived columns by their aliases in the expression, but you could use a derived table:

SELECT cola, colb, (expr1 - expr2) AS expr3 FROM
(
SELECT blah AS cola, blahblah AS colb,
(SELECT someConvultedExpressionHere FROM SomeTable) AS expr1,
(SELECT anotherConvultedExpressionHere FROM SomeTable) AS expr2
FROM tableA
WHERE lotsaConditions
) B

Owais

Make it idiot proof and someone will make a better idiot

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-10-03 : 09:00:30

Because the tables, joins and criteria are the same try this:

Select	sum(CASE WHEN loss_type = 'Y' THEN loss_amt ELSE 0 End) A
	, sum(CASE WHEN loss_type = 'N' THEN loss_amt ELSE 0 End) B
	, sum(CASE WHEN loss_type = 'Y' THEN loss_amt 
		   WHEN loss_type = 'N' THEN (-1*loss_amt) 
		   ELSE 0 end) C
from claim c
Inner Join location_xref x on c.ref_location = x.location_key 
Left Join Loss l on c.claim_key = l.loss_claim
where x.ref_loc1 = A56478 and
c.clm_status in ( 'R' , 'O') 
group by clm_nbr,clm_clmt,clm_incdate,clm_status

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-03 : 13:16:04

I gotta...that's sum calculation you got there....

too bad you couldn't use the sidewinder joins.....

Oh, and yeah, sample Data and DDL would help...

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-10-03 : 15:07:25

Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources