What is the difference between COUNT(*) and COUNT(column) when using LEFT JOIN?

COUNT(*) counts all rows including NULL values from the LEFT JOIN, giving incorrect results. COUNT(column) ignores NULL values. For customers without orders, COUNT(*) returns 1 but COUNT(order_id) returns 0, which is correct.

When do you use WHERE vs HAVING in queries with JOIN and GROUP BY?

WHERE filters rows before grouping and cannot use aggregate functions. HAVING filters groups after aggregation and can use aggregate functions. Use WHERE to filter base data, HAVING to filter aggregated results.

How do you avoid double-counting when joining multiple tables with aggregation?

Ensure correct join relationships through proper foreign keys. Use junction tables when needed. Be careful with one-to-many relationships that can multiply rows. Consider using DISTINCT or subqueries to prevent inflated aggregations.

Write a query to show each customer with their total order amount, including customers who never ordered (show 0).

SELECT c.customer_name,

COALESCE(SUM(o.total_amount), 0) as total_spent

FROM customers c

LEFT JOIN orders o ON c.customer_id = o.customer_id

GROUP BY c.customer_id, c.customer_name

ORDER BY total_spent DESC;

LEFT JOIN preserves all customers. COALESCE handles NULL from SUM when customers have no orders.

Find product categories with more than 10 orders and average order value above $50.

SELECT cat.category_name,

COUNT(o.order_id) as order_count,

AVG(o.total_amount) as avg_order_value

FROM categories cat

INNER JOIN products p ON cat.category_id = p.category_id

INNER JOIN order_items oi ON p.product_id = oi.product_id

INNER JOIN orders o ON oi.order_id = o.order_id

GROUP BY cat.category_id, cat.category_name

HAVING COUNT(o.order_id) > 10 AND AVG(o.total_amount) > 50

ORDER BY avg_order_value DESC;

Multiple JOINs to connect categories to orders. HAVING filters on both count and average aggregations.

Create a report showing employees with their sales performance, including those with no sales.

SELECT e.employee_name,

COUNT(o.order_id) as orders_count,

COALESCE(SUM(o.total_amount), 0) as total_sales,

COALESCE(AVG(o.total_amount), 0) as avg_order_value

FROM employees e

LEFT JOIN orders o ON e.employee_id = o.salesperson_id

WHERE e.department = 'Sales'

GROUP BY e.employee_id, e.employee_name

ORDER BY total_sales DESC;

LEFT JOIN includes all sales employees. COALESCE handles NULL aggregations for employees with no sales.