with cte_orders(row_id, parent_id, group_name) as (
select row_id, parent_id, group_name from Orders where row_id = @f
union all
select O.row_id, O.parent_id, O.group_name
from Orders O
join cte_orders on cte_orders.row_id = O.parent_id
)
select sum(price) total_pice from cte_orders
left join OrderItems on cte_orders.row_id = OrderItems.order_id ;
DECLARE @S FLOAT, @ORD INT = 1
SELECT @S = ISNULL(SUM(price),0) FROM OrderItems WHERE (order_id = @ORD)
SET @S = @S +
( SELECT ISNULL(SUM(OrderItems.price), 0)
FROM OrderItems
INNER JOIN Orders AS P1 ON OrderItems.order_id = P1.row_id
WHERE (P1.parent_id = @ORD))
SET @S = @S +
( SELECT ISNULL(SUM(OrderItems.price), 0)
FROM OrderItems
INNER JOIN Orders AS P1 ON OrderItems.order_id = P1.row_id
INNER JOIN Orders AS P2 ON P1.parent_id = P2.row_id
WHERE (P2.parent_id = @ORD))
SET @S = @S +
( SELECT ISNULL(SUM(OrderItems.price), 0)
FROM OrderItems
INNER JOIN Orders AS P1 ON OrderItems.order_id = P1.row_id
INNER JOIN Orders AS P2 ON P1.parent_id = P2.row_id
INNER JOIN Orders AS P3 ON P2.parent_id = P3.row_id
WHERE (P3.parent_id = @ORD))
SELECT @Sselect
customer_id,
Customers.name,
count(distinct order_id) all_orders,
count(distinct case when OrderItems.name = N'Кассовый аппарат' then order_id end) cash_machine_orders
from Orders
join OrderItems on Orders.row_id = OrderItems.order_id
join Customers on Customers.row_id = customer_id
where Orders.registered_at between '2020/01/01' and '2020/12/31'
group by Customers.name, customer_id
having count(distinct order_id) = count(distinct case when OrderItems.name = N'Кассовый аппарат' then order_id end)
;