How does GROUP BY work?

-- Count users per country
SELECT country, COUNT(*) as user_count
FROM users
GROUP BY country;

-- Result:
-- | country | user_count |
-- |---------|------------|
-- | USA     | 150        |
-- | UK      | 75         |
-- | Canada  | 50         |

SELECT
  category,
  COUNT(*) as total_products,
  SUM(price) as total_value,
  AVG(price) as avg_price,
  MIN(price) as min_price,
  MAX(price) as max_price
FROM products
GROUP BY category;

SELECT
  country,
  city,
  COUNT(*) as user_count
FROM users
GROUP BY country, city
ORDER BY country, user_count DESC;

-- Countries with more than 100 users
SELECT country, COUNT(*) as user_count
FROM users
GROUP BY country
HAVING COUNT(*) > 100;

-- Categories with average price > $50
SELECT category, AVG(price) as avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 50
ORDER BY avg_price DESC;

SELECT category, AVG(price) as avg_price
FROM products
WHERE status = 'active'      -- Filters rows BEFORE grouping
GROUP BY category
HAVING AVG(price) > 50       -- Filters groups AFTER grouping
ORDER BY avg_price DESC;