How can I add these fields?

Question

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Andy Warren SSC Guru Points: 119902 More actions · Answer 1

One way would be something like this:

Declare @AA int

Declare @CA int

select @AA=Amount where code='AA'

select @CA=Amount where code='CA'

select @AA+@CA

Andy

GivThx SSCommitted Points: 1758 More actions · Answer 2

Hi, Andy..

Thx.. it works for this ID but not other ID who doesnt have DA or DB..

I got Null return..

Any other way?

Jay

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Try pivoting first then add.

SELECT

[ID]

((AA+CA)-(DA+DB+EA)) AS MyCalc

FROM (

SELECT

[ID],

SUM(CASE CODE WHEN 'AA' THEN Amount ELSE 0 END) AS AA,

SUM(CASE CODE WHEN 'CA' THEN Amount ELSE 0 END) AS CA,

SUM(CASE CODE WHEN 'DA' THEN Amount ELSE 0 END) AS DA,

SUM(CASE CODE WHEN 'DB' THEN Amount ELSE 0 END) AS DB,

SUM(CASE CODE WHEN 'EA' THEN Amount ELSE 0 END) AS EA

FROM

tblX

GROUP BY

[ID]

) AS tblBaseData

Note: You may need to do 0 as 0.00 to keep datatype right or even CAST(0 AS numeric(3,2)) or similar.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Added END to correct for future reference.

Edited by - antares686 on 04/01/2002 3:23:51 PM

GivThx SSCommitted Points: 1758 More actions · Answer 4

Thx..

That really works...

In the code, U missed END after 0 :-)))

Anyway..Thx

Jay

Antares686 SSC Guru Points: 125444 More actions · Answer 5

Sorry doing this from the top of my head, no server to test against and no analyzer with nice color coding.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Deuce SSCrazy Points: 2712 More actions · Answer 6

To eliminate NULL when summing fields, use isnull(fieldname, 0) so you end up with:

sum(isnull(fieldname, 0))