Sum Quesiton

Transact-SQL
1

Hi guys,

In my project, I have two tables (BulkPurchaseRequests and BulkPurchases) to join and want to get only 1 result. The result should have ProductCode, TotalAmount, and RequestDateTime from BulkPurchaseRequests and Total of Amounts from BulkPurchases where Status = 0.

Here are the tables and sample data;

USE [GameAPI]
GO
/****** Object:  Table [dbo].[BulkPurchaseRequest]    Script Date: 12.06.2023 15:53:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BulkPurchaseRequest](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[TotalAmount] [int] NOT NULL,
	[ProductCode] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](max) NULL,
	[Email] [nvarchar](150) NULL,
	[Status] [int] NULL,
	[RequestDateTime] [datetime] NULL,
 CONSTRAINT [PK_dbo.BulkPurchaseRequest] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[BulkPurchases]    Script Date: 12.06.2023 15:53:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BulkPurchases](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[BulkPurchaseRequestId] [int] NOT NULL,
	[Amount] [int] NOT NULL,
	[ProductCode] [nvarchar](50) NOT NULL,
	[Status] [int] NULL,
	[PurchaseDateTime] [datetime] NULL,
 CONSTRAINT [PK_dbo.BulkPurchases] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[BulkPurchaseRequest] ON 
GO
INSERT [dbo].[BulkPurchaseRequest] ([Id], [TotalAmount], [ProductCode], [Description], [Email], [Status], [RequestDateTime]) VALUES (47, 10, N'018000007166', N'cenk', N'kizildagcenk@gmail.com', 1, CAST(N'2023-03-18T18:03:25.737' AS DateTime))
GO
INSERT [dbo].[BulkPurchaseRequest] ([Id], [TotalAmount], [ProductCode], [Description], [Email], [Status], [RequestDateTime]) VALUES (48, 6, N'0186434', N'test', N'kizildagcenk@gmail.com', 1, CAST(N'2023-03-18T19:05:32.900' AS DateTime))
GO
INSERT [dbo].[BulkPurchaseRequest] ([Id], [TotalAmount], [ProductCode], [Description], [Email], [Status], [RequestDateTime]) VALUES (49, 20, N'0187209', N'test2', N'kizildagcenk@gmail.com', 0, CAST(N'2023-03-18T19:05:41.490' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[BulkPurchaseRequest] OFF
GO
SET IDENTITY_INSERT [dbo].[BulkPurchases] ON 
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkPurchaseRequestId], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (232, 47, 10, N'018000007166', 1, CAST(N'2023-03-18T18:04:06.630' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkPurchaseRequestId], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (233, 48, 6, N'0186434', 1, CAST(N'2023-03-18T19:05:33.110' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkPurchaseRequestId], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (234, 49, 20, N'0187209', 1, CAST(N'2023-04-04T14:44:00.000' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkPurchaseRequestId], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (235, 49, 20, N'0187209', 1, CAST(N'2023-04-04T11:11:00.000' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkPurchaseRequestId], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (236, 49, 20, N'0187209', 0, CAST(N'2023-04-04T12:12:00.000' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[BulkPurchases] OFF
GO
2

I come up a query like this one, but I don't know if it's a perfect fit or any room for improvement.

SELECT
  bp.ProductCode,
  bp.TotalAmount,
  bp.RequestDateTime,
  SUM(bp2.Amount) AS TotalAmountFromBulkPurchases
FROM BulkPurchaseRequest bp
LEFT JOIN BulkPurchases bp2 ON bp.Id = bp2.BulkPurchaseRequestId
WHERE bp2.Status = 0
GROUP BY bp.ProductCode, bp.TotalAmount, bp.RequestDateTime
ORDER BY bp.ProductCode