Sum with ID

Question

Post reply

Sum with ID

girl_bj

SSCommitted

Points: 1731
More actions
September 30, 2015 at 1:46 am

#399115

IDCode Amount
999 07032.00
999 21187.00
888 226200.00
999 125829.00
If code is 1 means discount.
I would like to pull all column but the amount I get is different.
26200.00-(7032+1187+25829.00)
i did this:
select id,discount-amount total
from(
select id,sum(case when code(0,2) then amount end) amount
,sum(case when code(1) then amount end) discount
from tableA
)a
the result i get is different than i wanted.
where is my mistake?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 1

Please give the information whet you required in detail..which will help to give Correct one...

I think below one is you required..

Use group by for Id Column of Derived table and the join with Original Table based on ID & do your calculations...

select A.id,

CASE WHEN --B.Code=1 AND

B.discount>0 Then B.discount-A.amount

ELSE A.amount END total

from TblA JOin

(

select id,sum(case when code(0,2) then amount end) amount

,sum(case when code(1) then amount end) discount

from tableA

Group by Id

)B On B.Id=A.Id --And A.Code in (0,1)

Thanks,

Sasidhar P

girl_bj SSCommitted Points: 1731 More actions · Answer 2

girl_bj

SSCommitted

Points: 1731

September 30, 2015 at 3:32 am

#1830361

I tried but it appear more results row.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

girl_bj (9/30/2015)
IDCode Amount
999 07032.00
999 21187.00
888 226200.00
999 125829.00
If code is 1 means discount.
I would like to pull all column but the amount I get is different.
26200.00-(7032+1187+25829.00)
i did this:
select id,discount-amount total
from(
select id,sum(case when code(0,2) then amount end) amount
,sum(case when code(1) then amount end) discount
from tableA
)a
the result i get is different than i wanted.
where is my mistake?

There are several:

code(0,2) - do you have a function called Code()? If not, this will generate a syntax error.

Where is your GROUP BY?

Total isn't usually discount-amount

26200.00-(7032+1187+25829.00) doesn't make sense within the context of your problem description.

Please try rephrasing your requirement.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden