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,'A','MM1','QQ1',1000),
(2,'A','MM1','QQ1',1000),
(3,'A','MM1','QQ1',1000),
(4,'A','MM1','QQ1',1000),
(5,'A','MM1','QQ1',1000),
(6,'B','MM2','QQ2',1500),
(7,'B','MM2','QQ2',1500),
(8,'C','MM3','QQ3',2000),
(9,'C','MM3','QQ3',2000),
(10,'D','MM4','QQ4',5000);

select *
from TradeDiscountDetailStatistics


TradeDisscountStatisticsID
DiscountCode
DiscountType
CategoryType
DiscountPrice


1
A
MM1
QQ1
1000

2
A
MM1
QQ1
1000

3
A
MM1
QQ1
1000

4
A
MM1
QQ1
1000

5
A
MM1
QQ1
1000

6
B
MM2
QQ2
1500

7
B
MM2
QQ2
1500

8
C
MM3
QQ3
2000

9
C
MM3
QQ3
2000

10
D
MM4
QQ4
5000


SELECT 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.DiscountCode=A.Code
GROUP BY A.Code,A.StartDate,A.EndDate,B.DiscountPrice
ORDER BY A.Code ASC


Code
StartDate
EndDate
DiscountPrice
TatalDiscountPrice


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

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

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

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	A	MM1	QQ1	1000
2	A	MM1	QQ1	1000
3	A	MM1	QQ1	1000
4	A	MM1	QQ1	1000
5	A	MM1	QQ1	1000
6	B	MM2	QQ2	1500
7	B	MM2	QQ2	1500
8	C	MM3	QQ3	2000
9	C	MM3	QQ3	2000
10	D	MM4	QQ4	5000