Skip to content

OrderBy&GroupBy.md

Latest commit

 

History

History
302 lines (254 loc) · 5.64 KB

OrderBy&GroupBy.md

File metadata and controls

302 lines (254 loc) · 5.64 KB

ORDER BY & GROUP BY in SQL

ORDER BY Clause

What is ORDER BY?

The ORDER BY clause is used to sort the result set of a query in ascending or descending order based on one or more columns.

Syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...

Key Points:

  • ASC: Ascending order (default)
  • DESC: Descending order
  • Can sort by multiple columns
  • NULL values appear first in ASC, last in DESC

GROUP BY Clause

What is GROUP BY?

The GROUP BY clause groups rows that have the same values in specified columns into summary rows. It's typically used with aggregate functions.

Syntax:

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1

Common Aggregate Functions:

  • COUNT(): Number of rows
  • SUM(): Total sum
  • AVG(): Average value
  • MAX(): Maximum value
  • MIN(): Minimum value

Practical Examples

Step 1: Create Database and Table

CREATE DATABASE orderby;
USE orderby;

CREATE TABLE students(
    id INT,
    name VARCHAR(50),
    marks INT
);

Step 2: Insert Sample Data

INSERT INTO students VALUES
(1, 'Amit', 85),
(2, 'Riya', 92),
(3, 'Sourav', 75),
(4, 'Neha', 92);

INSERT INTO students VALUES
(5, 'Amit', 95),
(6, 'Riya', 67),
(7, 'Sourav', 55),
(8, 'Neha', 87);

SELECT * FROM students;

Sample Data Output:

| id | name   | marks |
|----|--------|-------|
| 1  | Amit   | 85    |
| 2  | Riya   | 92    |
| 3  | Sourav | 75    |
| 4  | Neha   | 92    |
| 5  | Amit   | 95    |
| 6  | Riya   | 67    |
| 7  | Sourav | 55    |
| 8  | Neha   | 87    |

ORDER BY Examples

1. Sort by Marks (Descending)

SELECT name, marks
FROM students
ORDER BY marks DESC;

Result: Shows students with highest marks first

| name   | marks |
|--------|-------|
| Amit   | 95    |
| Riya   | 92    |
| Neha   | 92    |
| Neha   | 87    |
| Amit   | 85    |
| Sourav | 75    |
| Riya   | 67    |
| Sourav | 55    |

2. Sort by Marks (Ascending)

SELECT name, marks
FROM students
ORDER BY marks ASC;

Result: Shows students with lowest marks first

| name   | marks |
|--------|-------|
| Sourav | 55    |
| Riya   | 67    |
| Sourav | 75    |
| Amit   | 85    |
| Neha   | 87    |
| Riya   | 92    |
| Neha   | 92    |
| Amit   | 95    |

GROUP BY Examples

1. Count Records per Student

SELECT name, COUNT(marks)
FROM students
GROUP BY name;

Purpose: Shows how many test records each student has

| name   | COUNT(marks) |
|--------|--------------|
| Amit   | 2            |
| Riya   | 2            |
| Sourav | 2            |
| Neha   | 2            |

2. Highest Marks per Student

SELECT name, MAX(marks)
FROM students
GROUP BY name;

Purpose: Shows the best performance of each student

| name   | MAX(marks) |
|--------|------------|
| Amit   | 95         |
| Riya   | 92         |
| Sourav | 75         |
| Neha   | 92         |

3. Sum of IDs per Student

SELECT name, SUM(id)
FROM students
GROUP BY name;

Purpose: Adds up all ID values for each student

| name   | SUM(id) |
|--------|---------|
| Amit   | 6       |
| Riya   | 8       |
| Sourav | 10      |
| Neha   | 12      |

4. Average Marks per Student

SELECT name, AVG(marks)
FROM students
GROUP BY name;

Purpose: Shows the average performance of each student

| name   | AVG(marks) |
|--------|------------|
| Amit   | 90.0000    |
| Riya   | 79.5000    |
| Sourav | 65.0000    |
| Neha   | 89.5000    |

Advanced Examples

Combining ORDER BY with GROUP BY

SELECT name, AVG(marks) as average_marks
FROM students
GROUP BY name
ORDER BY average_marks DESC;

Purpose: Show students ranked by their average performance

Multiple Column Sorting

SELECT name, marks
FROM students
ORDER BY name ASC, marks DESC;

Purpose: Sort by name first, then by marks within each name

GROUP BY with HAVING

SELECT name, AVG(marks) as avg_marks
FROM students
GROUP BY name
HAVING AVG(marks) > 80;

Purpose: Show only students with average marks above 80

Key Differences

Aspect ORDER BY GROUP BY
Purpose Sort results Group similar records
Output Same number of rows Reduced rows (grouped)
Used with Any SELECT query Aggregate functions
Position End of query Before ORDER BY

Important Rules

ORDER BY Rules:

  1. Always comes at the end of the query
  2. Can use column names or column positions (1, 2, 3...)
  3. Default is ascending order
  4. Can sort by columns not in SELECT list

GROUP BY Rules:

  1. All non-aggregate columns in SELECT must be in GROUP BY
  2. Usually used with aggregate functions
  3. Comes before ORDER BY clause
  4. Use HAVING instead of WHERE for grouped conditions

Common Mistakes to Avoid

❌ Wrong:

SELECT name, marks, AVG(marks)
FROM students
GROUP BY name;
-- Error: marks column not in GROUP BY

✅ Correct:

SELECT name, AVG(marks)
FROM students
GROUP BY name;

❌ Wrong:

SELECT name, AVG(marks)
FROM students
WHERE AVG(marks) > 80
GROUP BY name;
-- Error: Cannot use WHERE with aggregate functions

✅ Correct:

SELECT name, AVG(marks)
FROM students
GROUP BY name
HAVING AVG(marks) > 80;

Summary

  • ORDER BY: Use to sort your results in a specific order
  • GROUP BY: Use to summarize data by grouping similar records
  • Combine both: First GROUP BY to summarize, then ORDER BY to sort the summary
  • Remember: GROUP BY reduces rows, ORDER BY just rearranges them