SQL - lesson 5 | HackIndia Skills

SQL - lesson 5 | HackIndia Skills | HackIndia

Example

Start with rows

Imagine your feature stores orders for a college canteen. Each row is one order. You want totals per status for an admin screen.

-- seed data idea
orders
id | student_name | status     | amount_rupees
1  | Asha         | paid       | 80
2  | Kabir        | paid       | 120
3  | Meera        | pending    | 60
4  | Rohan        | cancelled  | 100
5  | Iqra         | paid       | 50

Example

Count each group

Use COUNT(*) when you want to count rows. This is good for badges, reports, and simple admin dashboards.

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

-- possible result
status    | order_count
paid      | 3
pending   | 1
cancelled | 1

Example

Add useful totals

Use SUM for money, AVG for averages, and MIN or MAX for ranges. Name your output columns clearly so your README is easy to read.

SELECT
  status,
  COUNT(*) AS order_count,
  SUM(amount_rupees) AS total_rupees,
  AVG(amount_rupees) AS average_rupees
FROM orders
GROUP BY status
ORDER BY total_rupees DESC;

-- possible result
status    | order_count | total_rupees | average_rupees
paid      | 3           | 250          | 83.333333
cancelled | 1           | 100          | 100
pending   | 1           | 60           | 60

Common mistake

Do not select a normal column that is not in GROUP BY. For example, selecting student_name while grouping only by status does not make sense. There are many students inside one status.

If you need student-level rows, do not group. If you need status-level summaries, group by status and aggregate the rest.

-- Avoid this
SELECT
  status,
  student_name,
  COUNT(*) AS order_count
FROM orders
GROUP BY status;

Tip

Filter in the right place

Use WHERE before GROUP BY to filter raw rows. For example, only orders from this month.

Use HAVING after GROUP BY to filter summary rows. For example, only statuses with at least 2 orders.

SELECT
  status,
  COUNT(*) AS order_count,
  SUM(amount_rupees) AS total_rupees
FROM orders
WHERE amount_rupees > 0
GROUP BY status
HAVING COUNT(*) >= 2;

Summarise with GROUP BY

Why summaries matter

One idea

Start with rows

Count each group

Add useful totals

Common mistake

Filter in the right place

Do this now

Quick check