Aggregations Cheat Sheet in Aggregations and Group By SQL

Aggregations

Consider the case of sports tournaments like cricket. Players’ performances are analysed based on their batting average, maximum number of sixes hit, the least score in a tournament, etc.

We perform aggregations in such scenarios to combine multiple values into a single value, i.e., individual scores to an average score.

Let’s learn more about aggregations to perform insightful analysis using the following database.

Database The database consists of a player_match_details table that stores the information of players' details in a match like name, match, score, year, number of fours and sixes scored.

Schema

1

2

3

4

5

6

7

8

player_match_details (

name VARCHAR(250),

match VARCHAR(10),

score INTEGER,

fours INTEGER,

sixes INTEGER,

year INTEGER

);

SQL

Aggregation Functions

Combining multiple values into a single value is called aggregation. Following are the functions provided by SQL to perform aggregations on the given data:

Aggregate Functions	Description
COUNT	Counts the number of values
SUM	Adds all the values
MIN	Returns the minimum value
MAX	Returns the maximum value
AVG	Calculates the average of the values

Syntax

1

2

3

4

5

SELECT

aggregate_function(c1),

aggregate_function(c2)

FROM

TABLE;

SQL

Note

We can calculate multiple aggregate functions in a single query.

Examples

1. Get the total runs scored by "Ram" from the
   player_match_details
   table.

1

2

3

4

5

6

SELECT

SUM(score)

FROM

player_match_details

WHERE

name = "Ram";

SQL

Output

SUM(score)
221

2. Get the highest and least scores among all the matches that happened in the year 2011.

1

2

3

4

5

6

7

SELECT

MAX(score),

MIN(score)

FROM

player_match_details

WHERE

year = 2011;

SQL

Output

MAX(score)	MIN(score)
75	62

COUNT Variants

• Calculate the total number of matches played in the tournament.

Variant 1

1

2

SELECT COUNT(*)

FROM player_match_details;

SQL

Variant 2

1

2

SELECT COUNT(1)

FROM player_match_details;

SQL

Variant 3

1

2

SELECT COUNT()

FROM player_match_details;

SQL

Output of Variant 1, Variant 2 and Variant 3

All the variants, i.e.,

Variant 1

,

Variant 2

and

Variant 3

give the same result:

18

Special Cases

• When
  SUM
  function is applied on non-numeric data types like strings, date, time, datetime etc.,
  SQLite
  DBMS returns 
  0.0
  and
  PostgreSQL
  DBMS returns
  None
  .

• Aggregate functions on strings and their outputs

  Aggregate Functions	Output
  MIN, MAX	Based on lexicographic ordering
  SUM, AVG	0 (depends on DBMS)
  COUNT	Default behavior

• NULL
  values are ignored while computing the aggregation values

• When aggregate functions are applied on only

  NULL
  values:

  Aggregate Functions	Output
  MIN	NULL
  MAX	NULL
  SUM	NULL
  COUNT	0
  AVG	NULL

Alias

Using the keyword

AS

, we can provide alternate temporary names to the columns in the output.

Syntax

1

2

3

4

5

6

SELECT

c1 AS a1,

c2 AS a2,

...

FROM

table_name;

SQL

Examples

• Get all the names of players with column name as "player_name".

1

2

3

4

SELECT

name AS player_name

FROM

player_match_details;

SQL

Output

player_name
Ram
Joseph
---

• Get the average score of players as "avg_score".

1

2

3

4

SELECT

AVG(score) AS avg_score

FROM

player_match_details;

SQL

Output

avg_score
60

Try it Yourself!

• Get the average score of "Ram" in the year 2011.
• Get the least score among all the matches.
• Get the highest score among the scores of all players in 2014.
• Get the total number of sixes hit as 
  sixes_hit

name	match	score	fours	sixes	year
Ram	RR vs SRH	62	2	7	2011
Joseph	SRH vs CSK	44	2	4	2012
Lokesh	DC vs DD	99	2	13	2013
David	SRH vs CSK	96	1	13	2014
Joseph	SRH vs CSK				2012
Viraj	RCB vs RR	53	2	5	2010
Shyam	MI vs RCB	75	2	9	2011
Stark	MI vs DC	75	2	9	2012
Stark	MI vs SRH				2012
Ram	RR vs MI	84	1	11	2013
Joseph	SRH vs RR	42	1	4	2014
David	SRH vs MI				2014
Ramesh	CSK vs RR	9	0	0	2010
Ram	RR vs DC	75	2	9	2011
Joseph	SRH vs MI	30	5	0	2012
Lokesh	DC vs RR	87	2	11	2013
Ram	RR vs SRH				2011
David	SRH vs MI	9	0	0	2014