Q&A for those committed to a vegan or vegetarian lifestyle and anyone interested in learning more
declare @CustOrder table ( id int, CreateDate date ) declare @DenominationOrder table ( id int, DenID int, OrderID int ) declare @Denomination table ( id int, amount decimal(12,2) ) -- order 1 total denominations 3, total amount = 250+250+250=750 -- order 2 total denominations 2, total amount = 250+250=500 insert into @CustOrder VALUES(1, '2012-04-01'), (2, '2012-04-02'); insert into @Denomination VALUES(1, 250), (2, 250), (3, 250), (4, 250), (5, 250); insert into @DenominationOrder VALUES(1, 1, 1), (2, 2, 1), (3, 3, 1), (4, 4, 2), (5, 5, 2); SELECT o.CreateDate, COUNT(o.id), SUM(d.amount) AS 'Total Amount' FROM @CustOrder o INNER JOIN @DenominationOrder do ON o.id = do.OrderID INNER JOIN @Denomination d ON do.DenId = d.id GROUP BY o.CreateDate