SQL query to summarize data

Question

I have 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 an SQL query to retrieve data.

The conditions apply IF pno the same way, and TaxTypeCode1 contains TaxTypeCode2, then sums amt, otherwise it displays the actual amt

My expected result

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

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

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

126 has 400 because pno is different and TaxTypeCode2 (TX02) TaxTypeCode1 (TX02) are the same, don’t summarize

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

Can anyone tell how to write a query to retrieve this data?

Answer 1

 SELECT PNO,SUM(Amount) FROM YOURTABLE GROUP BY PNO; 

Answer 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 the request for you:

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

The result corresponds to the expected result.

I found what you really do is aggregate data using PNO and the second or first column (if the second is empty). COALESCE(TaxTypeCode2, TaxTypeCode1) will return non-empty first.

You can also use ISNULL(TaxTypeCode2, TaxTypeCode1) . COALESCE can have more than two parameters, such as COALESCE(TaxTypeCode3, TaxTypeCode2, TaxTypeCode1) .

See that:

 SELECT COALESCE(TaxTypeCode2, TaxTypeCode1) as sumBy, * FROM Test