Group By

Sql tutorial · PySpark.in

4.2 GROUP BY and HAVING in SQL

When working with large amounts of data, we often need to:

• Combine multiple rows into summarized results
• Calculate totals, averages, counts, etc.
• Filter grouped results

SQL provides the following clauses for this purpose:

• GROUP BY
• HAVING

GROUP BY

The GROUP BY clause is used to group rows that contain the same values in specified columns.

It is commonly used with aggregation functions such as:

• COUNT()
• SUM()
• AVG()
• MIN()
• MAX()

Syntax

```

hide

SELECT column_name,
aggregate_function(column_name)
FROM table_name
GROUP BY column_name;

```

Example Table — player_match_details

Example: Calculate Total Score of Each Player

```

sql

CREATE TABLE player_match_details (

match_id INT,

name VARCHAR(50),

score INT

);

INSERT INTO player_match_details (match_id, name, score)

VALUES

(1, 'David', 45),

(2, 'David', 60),

(3, 'Joseph', 70),

(4, 'Joseph', 46),

(5, 'Lokesh', 95),

(6, 'Lokesh', 91);

SELECT name,

SUM(score) AS total_score

FROM player_match_details

GROUP BY name;

```

Output

How GROUP BY Works

Step 1 — Original Data

↓

Step 2 — Rows Are Grouped by name

↓

Step 3 — SUM(score) Is Applied

GROUP BY with WHERE

The WHERE clause is used to filter rows before grouping happens.

This means only the filtered rows participate in aggregation.

Syntax

```

hide

SELECT column_name,
aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name;

```

Example: Count Half Centuries Scored by Each Player

A half century means a score greater than or equal to 50.

```

sql

SELECT name,
COUNT(*) AS half_centuries
FROM player_match_details
WHERE score >= 50
GROUP BY name;

```

Output

Execution Flow

Step 1 — Apply WHERE Condition

```

hide

WHERE score >= 50

```

Filtered rows:

↓

Step 2 — GROUP BY name

↓

Step 3 — COUNT Rows

HAVING Clause

The HAVING clause is used to filter grouped data after GROUP BY is applied.

Unlike WHERE, HAVING works on aggregated results.

Syntax

```

hide

SELECT column_name,
aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;

```

Example: Players with More Than One Half Century

```

sql

SELECT name,
COUNT(*) AS half_centuries
FROM player_match_details
WHERE score >= 50
GROUP BY name
HAVING COUNT(*) > 1;

```

Output

Execution Flow

Step 1 — WHERE Filters Rows

```

hide

WHERE score >= 50

```

↓

Step 2 — GROUP BY Creates Groups

↓

Step 3 — COUNT Aggregation

↓

Step 4 — HAVING Filters Groups

```

hide

HAVING COUNT(*) > 1

```

Final Result:

WHERE vs HAVING

Important Difference

WHERE Filters Rows Before Grouping

```

sql

SELECT name, SUM(score)
FROM player_match_details
WHERE score > 50
GROUP BY name;

```

Only rows with score > 50 are grouped.

HAVING Filters Groups After Aggregation

```

sql

SELECT name, SUM(score) AS total_score
FROM player_match_details
GROUP BY name
HAVING SUM(score) > 100;

```

Here:

1. Rows are grouped first
2. Total score is calculated
3. Groups with total score greater than 100 are displayed

Output

Order of Execution in SQL Query

Understanding execution order is very important.

SQL executes queries in this order:

FROM
→ WHERE
→ GROUP BY
→ HAVING
→ SELECT
→ ORDER BY

More Sql tutorials

• Introduction to Databases
• Introduction To DBMS
• Introduction To SQL
• Natural Join
• INNER JOIN
• LEFT JOIN

All tutorials · Try the free PySpark compiler · Practice challenges