Sql query to get sum of Amounts based on the TaxType Code

I have my table datas as follows

EmpID    FedTaxID    TaxTypeCode   Amount
1059    888888888   TX02        2000
1059    888888888   TX02        3000
1059    888888888   TX03        5010

I am using self join to get the total amount based on FedTaxID and TaxTypeCode. I write as follows

SELECT SUM(t1.Amount) AS Expr1, SUM(t2.Amount) AS Expr2 FROM tblTest AS t1 CROSS JOIN tblTest AS t2 WHERE (t1.FedTaxID = '888888888'开发者_Python百科) AND (t2.FedTaxID = '888888888') AND (t1.TaxTypeCode = 'tx02') AND (t2.TaxTypeCode = 'tx03')

But it is returning the amount as follows

 Expr1      Expr2 
 5000   10020

My expected output is

 Expr1      Expr2 
  5000     5010

So can any one tell where i went wrong please. Also i need the total sum of those 2 so can any one help me on that too

---

Much better to use a "CASE" statement for this sort of thing:

SELECT FedTaxId, SUM(tx02t) as tx02_tot , SUM(tx03t) as tx03tot , SUM(Txallt) as txnntot
  FROM (SELECT CASE WHEN  TaxTypeCode = 'tx02' THEN Amount else 0 END as tx02t,
               CASE WHEN  TaxTypeCode = 'tx03' THEN Amount else 0 END as tx03t,
               Amount as txallt
        FROM tbltest
        WHERE FedTaxID = '888888888'
       ) 
GROUP BY FedTaxId

---

I don't have a handy SQL instance to check my syntax, but you might be able to do this using PIVOT:

Select ['TX02'], ['TX03']
FROM
(
    Select TaxTypeCode, Sum(Amount) as Total
    From tblTest
    Group By TaxTypeCode
) as SourceTable
PIVOT
(
Sum(Total)
FOR TaxTypeCode IN (['TX02'], ['TX03'])
) AS PivotTable

UPDATE: With FedTaxID (again, with a grain of salt, I don't have a way to check this right now):

Select ['TX02'], ['TX03']
FROM
(
    Select FedTaxID, TaxTypeCode, Sum(Amount) as Total
    From tblTest
    Group By FedTaxID, TaxTypeCode
) as SourceTable
PIVOT
(
Sum(Total)
FOR TaxTypeCode IN (['TX02'], ['TX03'])
) AS PivotTable

---

Just do a simple GROUP BY and use WITH ROLLUP for the totals:

SELECT 
    TaxTypeCode, SUM(Amount) 
FROM 
    tblTest 
WHERE
    FedTaxID = '888888888'
    AND TaxTypeCode IN ('TX02', 'TX03')
GROUP BY
    TaxTypeCode WITH ROLLUP

Should give you an output something like this:

 TaxTypeCode    (No column name)
    TX02           5000
    TX03           5010
    NULL          10010              -- this is the line with the totals

---

You're adding up the value twice, So expr1 = 2000 + 3000 = 5000

But expr you're adding also twice, so 5010 + 5010 = 10020.

Tjeu

---

Your cross join leads to two rows of data being returned, looking something like the following

2000 5010
3000 5010

So, if you sum those up you get the result you're seeing.

I'm sure it isn't the best way but subqueries would work.