COUNT, SUM, AVG, GROUP BY and HAVING in MySQL

Answers — GROUP BY and HAVING

1. Easy. Count all customers and known emails. Target: 6 customers and 5 known emails.

   SELECT COUNT(*) AS customers, COUNT(email) AS known_emails FROM customers;

2. Medium. Count products per category. Target: Electronics 2, Home 1, Stationery 3.

   SELECT category, COUNT(*) AS products FROM products GROUP BY category ORDER BY category;

3. Hard. Return only cities with more than one customer. Target: Mumbai 2 and Pune 2.

   SELECT city, COUNT(*) AS customers FROM customers GROUP BY city HAVING COUNT(*) > 1 ORDER BY city;

4. Professional. Produce an order-status reconciliation for an operations report. Target: completed 6, cancelled 1, pending 1; groups sum to 8. Do not call pending orders revenue.

   SELECT status, COUNT(*) AS orders FROM orders GROUP BY status ORDER BY status;
   SELECT COUNT(*) AS control_total FROM orders;

5. Easy. Count all products. Target: 1 row: products = 6

   SELECT COUNT(*) AS products FROM products;

6. Easy. Find the highest product price. Target: 1 row: highest = 1200.00

   SELECT MAX(price) AS highest FROM products;

7. Easy. Find the lowest product price. Target: 1 row: lowest = 20.00

   SELECT MIN(price) AS lowest FROM products;

8. Easy. Find the average product price. Target: 1 row: average_price = 445

   SELECT AVG(price) AS average_price FROM products;

9. Medium. Count orders per customer_id. Target: 5 rows: (1, 2); (2, 2); (3, 1); (4, 2); (5, 1)

   SELECT customer_id, COUNT(*) AS orders FROM orders GROUP BY customer_id ORDER BY customer_id;

10. Medium. Show total quantity sold per product_id across all order items. Target: 6 rows: (1, 6); (2, 15); (3, 3); (4, 5); (5, 2); (6, 2)

    SELECT product_id, SUM(quantity) AS units FROM order_items GROUP BY product_id ORDER BY product_id;

11. Medium. Show the average price per category. Target: 3 rows: Electronics 1000, Home 250, Stationery 140

    SELECT category, AVG(price) AS avg_price FROM products GROUP BY category ORDER BY category;

12. Medium. Count how many distinct cities customers live in. Target: 1 row: cities = 4

    SELECT COUNT(DISTINCT city) AS cities FROM customers;

13. Medium. Show how many items (rows) each order has. Target: 8 rows; 104 and 107 have 1, the rest have 2

    SELECT order_id, COUNT(*) AS line_count FROM order_items GROUP BY order_id ORDER BY order_id;

14. Hard. Show categories whose average price is above 400. Target: 1 row: Electronics, 1000

    SELECT category, AVG(price) AS avg_price FROM products GROUP BY category HAVING AVG(price) > 400;

15. Hard. Show customer_ids with 2 or more completed orders. Target: 3 rows: (1, 2); (2, 2); (4, 2)

    SELECT customer_id, COUNT(*) AS completed_orders FROM orders WHERE status = 'completed' GROUP BY customer_id HAVING COUNT(*) >= 2 ORDER BY customer_id;

16. Hard. Find product_ids that sold 5 or more units in total. Target: 3 rows: (1, 6); (2, 15); (4, 5)

    SELECT product_id, SUM(quantity) AS units FROM order_items GROUP BY product_id HAVING SUM(quantity) >= 5 ORDER BY product_id;

17. Hard. Show line value per order from order_items only (no status filter) and keep orders worth more than 1000. Target: 4 rows: 102 1300, 103 1400, 106 1300, 108 1300

    SELECT order_id, SUM(quantity * unit_price) AS order_value FROM order_items GROUP BY order_id HAVING SUM(quantity * unit_price) > 1000 ORDER BY order_id;

18. Professional. Category price summary for the buying team: count, min, max, average (2 decimals), most products first. Target: 3 rows: (Stationery, 3, 20, 300, 140.00); (Electronics, 2, 800, 1200, 1000.00); (Home, 1, 250, 250, 250.00)

    SELECT category, COUNT(*) AS products, MIN(price) AS min_price, MAX(price) AS max_price, ROUND(AVG(price), 2) AS avg_price FROM products GROUP BY category ORDER BY products DESC, category;

19. Professional. Orders per month (all statuses) with a control total that matches 8. Target: 3 rows: (2026-01, 2); (2026-02, 3); (2026-03, 3)

    SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, COUNT(*) AS orders FROM orders GROUP BY month ORDER BY month;