SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Discount 
(
	Code nvarchar(20),
	DiscountType nvarchar(10),
	CategoryType nvarchar(20),
	StartDate nvarchar(20),
	EndDate nvarchar(10)
);

insert into Discount
values
('A','MM1','QQ1','2020/5/3','2020/6/3'),
('B','MM2','QQ2','2020/5/3','2020/6/3'),
('C','MM3','QQ3','2020/5/4','2020/6/4'),
('D','MM4','QQ4','2020/5/4','2020/6/4'),
('E','MM5','QQ5','2020/5/4','2020/6/4');

select *
from Discount


Code
DiscountType
CategoryType
StartDate
EndDate


A
MM1
QQ1
2020/5/3
2020/6/3

B
MM2
QQ2
2020/5/3
2020/6/3

C
MM3
QQ3
2020/5/4
2020/6/4

D
MM4
QQ4
2020/5/4
2020/6/4

E
MM5
QQ5
2020/5/4
2020/6/4


CREATE TABLE TradeDiscountDetailStatistics 
(
        TradeDisscountStatisticsID int,
        DiscountCode nvarchar(20),
        DiscountType nvarchar(10),
        CategoryType nvarchar(20),
        DiscountPrice float
);

insert into TradeDiscountDetailStatistics
values
(1,'AX','MM1','QQ1',1000),
(2,'AX','MM1','QQ1',1000),
(3,'AX','MM1','QQ1',1000),
(4,'AX','MM1','QQ1',1000),
(5,'AX','MM1','QQ1',1000),
(6,'BX','MM2','QQ2',1500),
(7,'BX','MM2','QQ2',1500),
(8,'CX','MM3','QQ3',2000),
(9,'CX','MM3','QQ3',2000),
(10,'DX','MM4','QQ4',5000);

select *
from TradeDiscountDetailStatistics


TradeDisscountStatisticsID
DiscountCode
DiscountType
CategoryType
DiscountPrice


1
AX
MM1
QQ1
1000

2
AX
MM1
QQ1
1000

3
AX
MM1
QQ1
1000

4
AX
MM1
QQ1
1000

5
AX
MM1
QQ1
1000

6
BX
MM2
QQ2
1500

7
BX
MM2
QQ2
1500

8
CX
MM3
QQ3
2000

9
CX
MM3
QQ3
2000

10
DX
MM4
QQ4
5000


SELECT A.CategoryType,A.DiscountType,A.Code,A.StartDate,A.EndDate,B.DiscountPrice,
SUM(B.DiscountPrice) AS TatalDiscountPrice
-- FROM [HQInformation].[dbo].[TradeDiscountDetailStatistics] AS B
FROM [TradeDiscountDetailStatistics] AS B
-- LEFT JOIN [HQMasterFile].[dbo].[Discount] AS A
LEFT JOIN [Discount] AS A
ON B.CategoryType=A.CategoryType AND B.DiscountType=A.DiscountType 
GROUP BY A.CategoryType,A.DiscountType,A.Code,A.StartDate,A.EndDate,B.DiscountPrice
ORDER BY A.Code ASC


CategoryType
DiscountType
Code
StartDate
EndDate
DiscountPrice
TatalDiscountPrice


QQ1
MM1
A
2020/5/3
2020/6/3
1000
5000

QQ2
MM2
B
2020/5/3
2020/6/3
1500
3000

QQ3
MM3
C
2020/5/4
2020/6/4
2000
4000

QQ4
MM4
D
2020/5/4
2020/6/4
5000
5000

Code	DiscountType	CategoryType	StartDate	EndDate
A	MM1	QQ1	2020/5/3	2020/6/3
B	MM2	QQ2	2020/5/3	2020/6/3
C	MM3	QQ3	2020/5/4	2020/6/4
D	MM4	QQ4	2020/5/4	2020/6/4
E	MM5	QQ5	2020/5/4	2020/6/4

TradeDisscountStatisticsID	DiscountCode	DiscountType	CategoryType	DiscountPrice
1	AX	MM1	QQ1	1000
2	AX	MM1	QQ1	1000
3	AX	MM1	QQ1	1000
4	AX	MM1	QQ1	1000
5	AX	MM1	QQ1	1000
6	BX	MM2	QQ2	1500
7	BX	MM2	QQ2	1500
8	CX	MM3	QQ3	2000
9	CX	MM3	QQ3	2000
10	DX	MM4	QQ4	5000