Answer the question
In order to leave comments, you need to log in
How to calculate the amount of orders in subgroups?
The Customers table contains information about online store customers, the Orders table contains information about orders, and the OrderItems table contains items in orders. The data in the Orders table has a tree structure.
Records whose group_name field is not null are intended for grouping orders by category.
Such records are not associated with a customer, do not have a registration date and associated items in the OrderItems table,
but may contain orders or other groups. If the group_name field is null, then the record is an
order. It is necessarily associated with the buyer, has positions and registration date, but cannot have
nested orders. Records are associated with a parent group using the parent_id field, which stores
row_id of this group. Records with parent_id equal to null are considered root. An order can have multiple items
with the same name.
SELECT SUM(OrderItems.price) AS total FROM OrderItems
JOIN Orders ON OrderItems.order_id = Orders.row_id
WHERE Orders.parent_id = '2' OR Orders.row_id = '2' AND Orders.group_name IS NULL
--OR Orders.parent_id = (Select Orders.row_id FROM Orders where Orders.parent_id = '2' )
SELECT Customers.name FROM Customers
INNER JOIN Orders ON Customers.row_id = Orders.customer_id
INNER JOIN OrderItems ON Orders.row_id = OrderItems.order_id
WHERE Orders.registered_at > '2020/01/01'
AND OrderItems.name = ALL ( SELECT OrderItems.name FROM OrderItems WHERE OrderItems.name = 'Кассовый аппарат')
Answer the question
In order to leave comments, you need to log in
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question