MySQL Aggregate and Group

Write a query to show the total balance using debit and credit entries

Description: A table transactions has the columns id, account_id, type (credit or debit) and amount. Show the balance of each account. Credit adds money and debit takes money.

Hint:

• Use CASE to turn a debit into a negative amount.
• Then SUM() the amounts with GROUP BY account_id.

MySQL query for the balance from debit and credit

-- sample data
CREATE TABLE transactions (id INT, account_id INT, type VARCHAR(10), amount INT);
INSERT INTO transactions VALUES
  (1, 1, 'credit', 1000),
  (2, 1, 'debit', 200),
  (3, 2, 'credit', 500),
  (4, 2, 'debit', 100),
  (5, 1, 'credit', 300);

-- the query
SELECT account_id,
       SUM(CASE WHEN type = 'credit' THEN amount ELSE -amount END) AS balance
FROM transactions
GROUP BY account_id
ORDER BY account_id;

Output:

How it works

• For account 1: 1000 – 200 + 300 = 1100.
• For account 2: 500 – 100 = 400.
• CASE works like if else inside a query.

Write a query to show the count of students by city

Description: Show the number of students in each city.

Tables: the exercises use the students (id, name, city, marks, course_id) and courses (id, course_name) tables, with the data from our MySQL INSERT chapter. The students are: Robin (Delhi, 85), Maria (Mumbai, 92), John (Pune, 78), Asha (Jaipur, 88), Vikram (Delhi, 65), Neha (Mumbai, 45).

Hint:

• Use COUNT(*).
• Group the rows with GROUP BY city.

MySQL query for the count of students by city

SELECT city, COUNT(*) AS total_students
FROM students
GROUP BY city
ORDER BY city;

Output:

How it works

• GROUP BY city makes one group for each city.
• COUNT(*) counts the rows of each group.

Read GROUP BY and HAVING.

Write a query to show the average marks of each city

Description: Show each city with the average marks of its students.

Tables: the exercises use the students (id, name, city, marks, course_id) and courses (id, course_name) tables, with the data from our MySQL INSERT chapter. The students are: Robin (Delhi, 85), Maria (Mumbai, 92), John (Pune, 78), Asha (Jaipur, 88), Vikram (Delhi, 65), Neha (Mumbai, 45).

Hint:

• Use AVG(marks) with GROUP BY city.
• Use ROUND() to show 2 decimals.

MySQL query for the average marks by city

SELECT city, ROUND(AVG(marks), 2) AS average_marks
FROM students
GROUP BY city
ORDER BY city;

Output:

How it works

• For Delhi: (85 + 65) / 2 = 75.
• For Mumbai: (92 + 45) / 2 = 68.5.

Read Aggregate Functions.

Write a query to find the cities that have more than one student

Description: Show only those cities where two or more students live.

Tables: the exercises use the students (id, name, city, marks, course_id) and courses (id, course_name) tables, with the data from our MySQL INSERT chapter. The students are: Robin (Delhi, 85), Maria (Mumbai, 92), John (Pune, 78), Asha (Jaipur, 88), Vikram (Delhi, 65), Neha (Mumbai, 45).

Hint:

• Group by city, then filter the groups with HAVING COUNT(*) > 1.

MySQL query for cities with more than one student

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

Output:

How it works

• WHERE cannot use COUNT(), because it runs before the grouping.
• HAVING filters the groups after they are made.

Read GROUP BY and HAVING.

Write a query to fetch the number of students from Delhi

Description: Count the students who live in Delhi.

Tables: the exercises use the students (id, name, city, marks, course_id) and courses (id, course_name) tables, with the data from our MySQL INSERT chapter. The students are: Robin (Delhi, 85), Maria (Mumbai, 92), John (Pune, 78), Asha (Jaipur, 88), Vikram (Delhi, 65), Neha (Mumbai, 45).

Hint:

• Use COUNT(*) with WHERE city = 'Delhi'.

MySQL query to count the students of Delhi

SELECT COUNT(*) AS students_from_delhi
FROM students
WHERE city = 'Delhi';

Output:

How it works

• WHERE keeps only the Delhi rows: Robin and Vikram.
• COUNT(*) counts them.

Read Aggregate Functions.