Group By with Having Cheat Sheet in Aggregations and Group By SQL

 Group By with Having

Previously, we have learnt to perform aggregations on all the rows of a table. Now, we shall look at how to split a table into multiple groups and apply aggregation on each group.

The GROUP BY keyword in SQL is used to group rows which have the same values for the mentioned attributes. You can perform aggregations on these groups to get finer analytics.

HAVING keyword is used to further refine the data by filtering the aggregated values. Let’s explore more about GROUP BY and HAVING clauses with the following database.

Database

The database consists of

player_match_details

table which stores name, match, score, year, number of fours and sixes scored.

• Schema

1

2

3

4

5

6

7

8

CREATE TABLE player_match_details (

name VARCHAR(250),

match VARCHAR(250),

score INTEGER,

fours INTEGER,

sixes INTEGER,

year INTEGER

);

SQL

GROUP BY

The

GROUP BY

clause in SQL is used to group rows which have same values for the mentioned attributes.

Syntax

1

2

3

4

5

6

SELECT

c1,

aggregate_function(c2)

FROM

table_name

GROUP BY c1;

SQL

Example

Get the total score of each player in the database.

1

2

3

4

5

SELECT

name, SUM(score) as total_score

FROM

player_match_details

GROUP BY name;

SQL

Output

name	total_score
David	105
Joseph	116
Lokesh	186
...	...

Try it Yourself!

• Get the maximum score of each player.
• Get the total number of sixes hit by each player.

GROUP BY with WHERE

We use WHERE clause to filter the data before performing aggregation.

Syntax

1

2

3

4

5

6

7

8

SELECT

c1,

aggregate_function(c2)

FROM

table_name

WHERE

c3 = v1

GROUP BY c1;

SQL

Example

Get the number of half-centuries scored by each player

1

2

3

4

5

6

SELECT

name, COUNT(*) AS half_centuries

FROM

player_match_details

WHERE score >= 50

GROUP BY name;

SQL

Output

name	half_centuries
David	1
Joseph	2
Lokesh	3
...	...

Try it Yourself!

• Get year wise number of half-centuries scored by each player.

HAVING

HAVING

clause is used to filter the resultant rows after the application of

GROUP BY

clause.

Syntax

1

2

3

4

5

6

7

8

9

10

SELECT

c1,

c2,

aggregate_function(c1)

FROM

table_name

GROUP BY

c1, c2

HAVING

condition;

SQL

Example

Get the

name

and number of

half_centuries

of players who scored more than one half century.

1

2

3

4

5

6

7

8

9

10

11

SELECT

name,

count(*) AS half_centuries

FROM

player_match_details

WHERE

score >= 50

GROUP BY

name

HAVING

half_centuries > 1;

 

SQL

Expand

Output

name	half_centuries
Lokesh	2
Ram	3

Try it Yourself!

• Get the name and number of half-centuries scored by each player who scored at least a half-century in two matches.

Note

WHERE vs HAVING: WHERE is used to filter rows and this operation is performed before grouping. HAVING is used to filter groups and this operation is performed after grouping.