select t1.[id заказа], sum(t2.[Стоимость]) as total_price
from СвязьТоваровЗаказов as t1
inner join Товары as t2
on t2.[id] = t1.[id товара]
group by t1.[id заказа]
DECLARE @today as Date, @yesterday as Date;
Set @today = convert(date, getdate());
Set @yesterday = convert(date, dateadd(day, -1, getdate()));
SELECT m.Name, o.Created,
Count(DISTINCT(CASE WHEN Status = 'N' And o.Date = @today Then i.ID END)) as NewQ,
Count(DISTINCT(CASE WHEN Status = 'N' And o.Date = @yesterday Then i.ID END)) as YdNewQ,
Count(DISTINCT(CASE WHEN Status = 'W' And o.Date = @today Then i.ID END)) as WaitingQ,
Count(DISTINCT(CASE WHEN Status = 'W' And o.Date = @yesterday Then i.ID END)) as YDWaitingQ,
Count(DISTINCT(CASE WHEN Status = 'U' And o.Date = @today Then i.ID END)) as ProblemQ,
Count(DISTINCT(CASE WHEN Status = 'U' And o.Date = @yesterday Then i.ID END)) as YdProblemQ,
Count(DISTINCT(CASE WHEN Status = 'Z' And o.Date = @today Then i.ID END)) as CancelledQ,
Count(DISTINCT(CASE WHEN Status = 'Z' And o.Date = @yesterday Then i.ID END)) as YdCancelledQ
FROM Orders i
LEFT JOIN OrderItems o ON o.OrderID = i.ID
LEFT JOIN NomenclUS m ON m.ID = o.ProductID
WHERE i.Status <> 'Z' AND o.Created >= dateadd(day, -2, getdate())
GROUP BY o.Created, m.CatID, m.CatName,m.Name
ORDER BY o.Created, m.CatID
Вот так как-то. Но если не верите проведите эксперимент, сделайте оба варианта запроса и сравните их планы.