Need Help in solving SQL QUERY

Question

I have my table data as follows

EMPID	FedTaxID	TaxType	Amount
1059	888888888	TX02	2000
1059	888888888	TX02	1000
1059	888888888	TX03	2000

I write a query to sum up the amount as follows

select SUM(Amount)
FROM
   tblTest
WHERE
   FedTaxID = '888888888'
   AND
   TaxTypeCode IN ('TX02', 'TX03')

But what i need is if TX03 exists 1 in the table i would like to add the amount multiple times means if i sum the total i will get 5000 but i would like to get 7000. If TX03 exists 1 time in the table i would like to add multiple times any idea please

Satish Bhat · Answer

2nd Solution SELECT CASE WHEN (SELECT COUNT(TaxTypeCode) FROM tblTest WHERE TaxTypeCode = 'TX03' AND FedTaxID = '888888888' ) = 1 THEN SUM(CASE WHEN TaxTypeCode = 'TX03' THEN (Amount*2) ELSE Amount END) ELSE SUM(Amount) END FROM tblTest WHERE FedTaxID = '888888888'

Dorababu Meka · Answer

Thanks Both of you

Suthish Nair · Answer

try this.. #test change to your table name with taxtype as ( select EMPID COUNT(1) cnt from #test where TaxType = 'TX03' group by EMPID ) taxtype1 as ( select ROW_NUMBER() over (order by EMPID) rn EMPID amount from #test where TaxType = 'TX03' ) select case when tt.cnt = 1 then tt1.amount + sum(t.Amount) else sum(t.Amount) end Amount FROM #test t inner join taxtype tt on t.EMPID = tt.EMPID and t.FedTaxID = '888888888' inner join taxtype1 tt1 on t.EMPID = tt1.EMPID and tt1.rn = tt.cnt group by tt.cnt tt1.amount

Dorababu Meka · Answer

Hi satish if i have multiple values i don't want to add the amount for multiple values as per your post i need as follows when i have multiple TX03 2000 + 1000 + 2000 + 1500 =6500

Satish Bhat · Answer

Please explain the expected result in the following data sample: EMPID FedTaxID TaxType Amount 1059 888888888 TX02 2000 1059 888888888 TX02 1000 1059 888888888 TX03 2000 1059 888888888 TX03 1500 What is your expected result? Is it 2000 + 1000 + (2000 * 2) + (1500 * 2)

Dorababu Meka · Answer

Ok let me execute it. It works but if i have multiple TX03 then my query should execute means if i have TX03 data for multiple then as per satish given this is not working fine

Suthish Nair · Answer

So Satish reply will help you

Dorababu Meka · Answer

If i have multiple rows then there is no problem right it will add all. We fixed maximum entries for individual TaxTypecode to 2 . So if i have multiple TX03 then my query works fine. If in case i have 2 TX02 and 1 TX03 i would like to add the amount at TX03 for multiple times

Suthish Nair · Answer

I agree with your thread but what happened you have multiple rows for TX03.

Satish Bhat · Answer

If the check is for only 'TX03' then you can use the following code: SELECT SUM(CASE WHEN TaxTypeCode = 'TX03' THEN (Amount*2) ELSE Amount END) FROM tblTest WHERE FedTaxID = '888888888' AND TaxTypeCode IN ('TX02' 'TX03')

Dorababu Meka · Answer

Sample output is what i said if i have multiple TX02 value and a single TX03 values i should add the value of TX03 for multiple times. As i have TX02 values as 2000 and 1000 and TX03 as 2000 as per my query i will get 5000 as output but i would like to get 7000

Suthish Nair · Answer

Can you post a sample o/p. Also the check is for only TX03?

Dorababu Meka · Answer

You can see my data in that at TaxTypecode there exists 2 different values like TX02 and TX03. So i have to write a condition in such a way that if Exists TX03 is 1 in the data then i would like to sum itself multiple times means if values is 500 i need to get 1000 and then add to the remaining values as per i written in the query

Jiteendra Sampathirao · Answer

hi Dorababu what does this mean if TX03 exists 1 in the table