SQL Aggregate Functions: COUNT, SUM, MIN, MAX, AVG

Classified in Computers

Written at on English with a size of 12.36 KB.

Aggregations

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

In such scenarios, we perform aggregations to combine multiple values into a single value, i.e., individual scores into 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 information about players' details in a match, such as name, match, score, year, and the number of fours and sixes scored.

Schema

12345678
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 FunctionsDescription
COUNTCounts the number of values
SUMAdds all the values
MINReturns the minimum value
MAXReturns the maximum value
AVGCalculates the average of the values

Syntax

12345
SELECTaggregate_function(c1),aggregate_function(c2)FROMTABLE;
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.
aggregation_sum.gif
123456
SELECTSUM(score)FROMplayer_match_detailsWHEREname = "Ram";
SQL
Output
SUM(score)
221
  1. Get the highest and lowest scores among all the matches that happened in the year 2011.
1234567
SELECTMAX(score),MIN(score)FROMplayer_match_detailsWHEREyear = 2011;
SQL
Output
MAX(score)MIN(score)
7562

COUNT Variants

  • Calculate the total number of matches played in the tournament.
Variant 1
12
SELECT COUNT(*)FROM player_match_details;
SQL
Variant 2
1
2
SELECT COUNT(1)
FROM player_match_details;
SQL
Variant 3
12
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 the SUM function is applied to non-numeric data types like strings, date, time, datetime, etc., the SQLite DBMS returns 0.0, and the PostgreSQL DBMS returns None.

  • Aggregate functions on strings and their outputs:

    Aggregate FunctionsOutput
    MIN, MAXBased on lexicographic ordering
    SUM, AVG0 (depends on DBMS)
    COUNTDefault behavior
  • NULL values are ignored while computing the aggregation values.

  • When aggregate functions are applied to only NULL values:

    Aggregate FunctionsOutput
    MINNULL
    MAXNULL
    SUMNULL
    COUNT0
    AVGNULL

Alias

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

Syntax

123456
SELECTc1 AS a1,c2 AS a2,
FROMtable_name;
SQL

Examples

  • Get all the names of players with the column name as "player_name".
1234
SELECTname AS player_nameFROMplayer_match_details;
SQL
Output
player_name
Ram
Joseph
---
  • Get the average score of players as "avg_score".
1234
SELECTAVG(score) AS avg_scoreFROMplayer_match_details;
SQL
Output
avg_score
60

Try it Yourself!

  • Get the average score of "Ram" in the year 2011.
  • Get the lowest 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
Karma: 8%
Visits: 0

Entradas relacionadas:

Tags:
SQL Aggregate Functions COUNT SUM