How to do subqueries in SQL?

Question

MySQL

Good afternoon.

There are 2 tables in the database: sales (1000 records) and stores (2 records) - they are linked by ID.

tell me how to calculate sales by stores more correctly:
1) count all stores (2pcs) and then go to sales with a subquery and count them
2) or better, make a group buy by stores in sales and then rush into the table stores (2pcs) and take out the necessary data

Thank you

Answer 1

Correctly index tables and use JOIN instead of subqueries:

SELECT shop.id, shop.name, SUM(sales.amount) sales_amount
FROM sales
JOIN shops ON sales.shop_id = shops.id
GROUP BY  sales.shop_id, shop.id, shop.name