SQLize | PHPize | SQLtest

CREATE TABLE sma_products ( id INT AUTO_INCREMENT PRIMARY KEY, sku VARCHAR(50) NOT NULL, name VARCHAR(100) NOT NULL ); CREATE TABLE sma_sale_items ( id INT AUTO_INCREMENT PRIMARY KEY, sale_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL ); INSERT INTO sma_products (sku, name) VALUES ('P-001', 'Widget A'), ('P-002', 'Widget B'), ('P-003', 'Widget C'), ('P-004', 'Widget D'); INSERT INTO sma_sale_items (sale_id, product_id, quantity) VALUES (100, 1, 2), (101, 1, 1), (102, 1, 5), (103, 2, 4), (104, 3, 1), (105, 3, 3); SELECT p.*, COALESCE(s.count, 0) count FROM sma_products p LEFT JOIN ( SELECT product_id id, COUNT(1) count FROM sma_sale_items GROUP BY product_id ) s USING (id) ORDER BY count DESC;