Why should you care?

Flipkart merchandising asks “revenue per category this month” — one row per category, not millions of line items.

See it live — copy this example

Run in pgAdmin or psql.

SELECT
  category,
  COUNT(*) AS order_lines,
  SUM(line_total) AS revenue
FROM order_items
GROUP BY category
ORDER BY revenue DESC;

What happened?

• Rows with the same category merge into one group.
• COUNT and SUM compute per-group stats.
• ORDER BY sorts the summary, not raw line items.

Practice next

1. Create order_items(order_id, category, line_total) with sample data.
2. Run the GROUP BY query.
3. Try selecting product_name without grouping — read the error.
4. Use DATE_TRUNC('month', created_at) in GROUP BY for monthly totals.
5. Add COUNT(DISTINCT customer_id) if customer_id is on the table.

Remember

GROUP BY produces one row per distinct group key. Aggregates summarize inside each group. Filter groups with HAVING, not WHERE on aggregates.

PostgresVerse category report

Category managers pull weekly revenue by category for inventory planning.

Outcome: Electronics restock triggers before stockout during sale week.