SQL query to sum the data

问题:

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?

回答1:

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

回答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