SQL Query

SELECT TOP 1 p.PromotionId AS [Promotion ID], p.PromotionName AS [Promotion Name], SUM(oi.LineTotal) AS [Total Revenue] FROM dbo.ProductPromotions pp JOIN dbo.Promotions p ON pp.PromotionId = p.PromotionId JOIN dbo.OrderItems oi ON pp.ProductId = oi.ProductId GROUP BY p.PromotionId, p.PromotionName ORDER BY [Total Revenue] DESC, p.PromotionId