sum and avg using subquerys

select * from Sales;
select * From Products;
select P.ProductId,P.ProductName,P.Category,P.Price ,SP.TotalSales,SP.AvgSales from Products P
join ( select S.ProductID,SUM(Quantity * P.Price)as TotalSales,AVG(S.Quantity * P.Price)as AvgSales from Sales S join Products P
 on S.ProductID=P.ProductID group by S.ProductID ) SP on P.ProductID=SP.ProductID;

 --sub query for total and avg sales and outer query you need product name and category

 
SaleID	ProductID	SaleDate	Quantity
1		1			2023-08-01	5
2		2			2023-08-03	10
3		1			2023-08-10	3
4		3			2023-09-01	7
5		4			2023-09-15	4
6		2			2023-09-20	6
7		5			2023-10-05	15
8		5			2023-10-10	10


ProductID	ProductName	Category	Price
1	Laptop	Electronics				1200.00
2	Smartphone	Electronics			800.00
3	Desk Chair	Furniture			150.00
4	Coffee Table	Furniture		200.00
5	Headphones	Electronics			150.00


1	Laptop	Electronics	1200.00	9600.00	4800.000000
2	Smartphone	Electronics	800.00	12800.00	6400.000000
3	Desk Chair	Furniture	150.00	1050.00	1050.000000
4	Coffee Table	Furniture	200.00	800.00	800.000000
5	Headphones	Electronics	150.00	3750.00	1875.000000