0

I have my table data as follows

TaxTypeCode1   TaxTypeCode2  PNO   Amount 
-----------------------------------------
TX01           TX02           124     600
TX02           null           124     700 
TX03           TX04           124     200 
TX04           null           124     300
TX05           TX06           126     400 
TX06           null           127     500 
TX07           null           128     800

I would like to write SQL query to retrieve data.

Conditions apply IF pno is same and TaxTypeCode1 contain TaxTypeCode2 then sum the amt, otherwise display actual amt

My expected output is 

PNO      Amount 
---------------
 124     1300
 124      500
 126      400
 127      500
 128      800

124 has 1300 because pno is same and TaxTypeCode2 (TX02) TaxTypeCode1 (TX02) are same then sum 

TX01           (TX02)           124     600
(TX02)           null           124     700

126 has 400 because pno is different and TaxTypeCode2 (TX02) TaxTypeCode1 (TX02) are same don't sum 

TX05           (TX06)           (126)     400 
(TX06)           null           (127)     500

Can anyone tell how to write query to retrieve that data?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
r mythri
  • 1
  • 1
  • 3
  • This is not all that clear. Is this a single table you're talking about? Also, I would suggest putting a bit more effort into formatting the table. Try including it in a code block, where you can separate the individual rows by line more clearly. – Ben Barden Apr 13 '12 at 14:10
  • The GROUP BY statement is what you are looking for. Vutukuri has given the exact statement I would have used with the data you provided. I'm not sure what you were getting at with the TaxTypes, but grouping on your PNO's alone would give you the desired outcome. It might be that within your actual data, PNO is all you need to consider? – David Welker Apr 13 '12 at 14:29
  • Will you ever have a case where Record A will have a TaxCodeType2 that will match Record B's TaxCodeType1 and Record B's TaxCodeType2 will not be null? If so, do you need to keep linking records based on that link? – Jeremy Pridemore Apr 13 '12 at 15:11

2 Answers2

6
SELECT PNO,SUM(Amount)
FROM YOURTABLE
GROUP BY PNO;
Teja
  • 13,214
  • 36
  • 93
  • 155
  • situations like this, it will be better if we see what the person has tried for us to help him/her...thats the only way they can learn – dansasu11 Apr 13 '12 at 14:22
  • TaxTypeCode1 TaxTypeCode2 PNO Amount TX01 TX02 124 600 TX02 null 124 700 TX03 TX04 124 200 TX04 null 124 300 TX05 TX06 126 400 TX06 null 127 500 TX07 null 128 800 then out put will be – r mythri Apr 13 '12 at 14:24
  • PNO Amount 124 1300 124 500 126 400 127 500 128 800 – r mythri Apr 13 '12 at 14:26
  • Can you post your question by editing the existing one.It doesn't make any sense to me what you posted.. – Teja Apr 13 '12 at 14:27
2

This is your table and data:

CREATE TABLE Test
(
    TaxTypeCode1 CHAR(4),
    TaxTypeCode2 CHAR(4),
    PNO INT,
    Amount INT
)

INSERT INTO Test VALUES('TX01', 'TX02', 124, 600)
INSERT INTO Test VALUES('TX02', null, 124, 700)
INSERT INTO Test VALUES('TX03', 'TX04', 124, 200)
INSERT INTO Test VALUES('TX04', null, 124, 300)
INSERT INTO Test VALUES('TX05', 'TX06', 126, 400)
INSERT INTO Test VALUES('TX06', null, 127,500)
INSERT INTO Test VALUES('TX07', null, 128, 800)

and this is query for you:

SELECT PNO, SUM(Amount)
FROM Test
GROUP BY PNO, COALESCE(TaxTypeCode2, TaxTypeCode1)

Result matches your expected output.

I found that you really do is aggregating data by PNO and by second or first column (if second is empty). COALESCE(TaxTypeCode2, TaxTypeCode1) will return first not empty.

You can also use ISNULL(TaxTypeCode2, TaxTypeCode1). COALESCE can have more than 2 params like COALESCE(TaxTypeCode3, TaxTypeCode2, TaxTypeCode1).

See that:

SELECT COALESCE(TaxTypeCode2, TaxTypeCode1) as sumBy, * FROM Test
huhu78
  • 379
  • 2
  • 13