Skip to content

Aggregate Functions

cbjjensen edited this page Oct 14, 2014 · 4 revisions

SQL Aggregate Functions

AVG COUNT FIRST LAST MAX MIN SUM

SQL aggregate functions return a single value, calculated from values in a column.

These functions ignore NULL values.

For these examples, they will be using the INVENTORY table defined below.

NAME QUANTITY PRICE PRODUCT_ID
Box of Crackers 100 1.25 5841976320
Bottled Water 50 0.75 2704831956
Headphones 75 10.00 3671580924
Cellular Smart Phone 100 500.00 6103495827
Brand New Video Game 500 60.00 NULL
Monster® Energy Drink 25 2.00 8429071365
Monster® Energy Drink 23 2.00 8429071366
Baseball 100 2.00 1587432096
Mountain Dew® Voltage™ 50 1.25 1305764928
Cheddar Cheese 25 1.00 4631078925
Cheddar Cheese 10 5.55 7023615849
Cheddar Cheese 32 2.30 9801072326
Cheddar Cheese 12 3.29 6673246795
Cheddar Cheese 15 7.43 5938929012
Box of Dozen Donuts 30 4.99 9283740651

AVG()

EXAMPLES


COUNT()

  • Returns the number of items in a column.
  • Does not count null columns

Syntax:

COUNT(COLUMN_NAME)

EXAMPLES

SELECT COUNT(PRODUCT_ID)
FROM INVENTORY;

Output:

COUNT(PRODUCT_ID)
15

Count number of occurrences, and display count as separate column

SELECT NAME, COUNT(*)
FROM INVENTORY
GROUP BY NAME

Output: (to preserve space, I will only show some rows)

NAME COUNT(*)
Box of Crackers 1
Bottled Water 1
Monster® Energy Drink 2
Cheddar Cheese 5
Box of Dozen Donuts 1

Limit Output to display a range

SELECT NAME, COUNT(*)
FROM INVENTORY
GROUP BY NAME
HAVING COUNT(*) => 2 AND COUNT(*) <=5

Output:

NAME COUNT(*)
Monster® Energy Drink 2
Cheddar Cheese 5

FIRST()

EXAMPLES


LAST()

EXAMPLES


MAX()

EXAMPLES


MIN()

  • Returns the minimum value of the set of data.

Syntax:

MIN(COLUMN_NAME)

EXAMPLES

SELECT NAME, PRICE
FROM INVENTORY
WHERE PRICE = (SELECT MIN(PRICE) FROM INVENTORY)

Outputs:

NAME PRICE
Bottled Water 0.75

SUM()

  • Returns the sum of all of the values in the passed-in column.

Syntax:

SUM(COLUMN_NAME)

EXAMPLES

SELECT SUM(PRICE)
FROM INVENTORY;

Outputs:

SUM(PRICE)
601.81

Format Sum as Dollar

SELECT TO_CHAR(SUM(PRICE), '$999,999.00') as Sum_Formatted
from student.course_revenue;

Output:

Sum_Formatted
$601.81

Clone this wiki locally