Aggregates in SQL are eerily similar to the build-out of aggregate formulas in spreadsheet software. Here's how you'd grab the total amount of payments from the Getting Started Payments dataset:
SELECT
SUM(payment_amount)
FROM getting_started.payments
;
-- The query above will output a new column is titled 'sum'
-- To customize the name of the new column, use AS [new column name]
SELECT
SUM(payment_amount) AS total_payment_amount
FROM getting_started.payments
;
Calculating the average Payment Amount, and calling this output field 'avg_payment_amount':
SELECT
AVG(payment_amount) AS avg_payment_amount
FROM getting_started.payments
;
-- The query above will product an output with a lot of decimal places
-- We can use the ROUND operator to dictate the number of decimal places
-- For example, if we want 0 decimal places:
SELECT
ROUND(AVG(payment_amount), 0) AS avg_payment_amount
FROM getting_started.payments
;
-- Or, if we want to see 0, 2, and 4 decimal places all in the same query:
SELECT
ROUND(AVG(payment_amount), 0) AS avg_payment_amount_0_decimal
, ROUND(AVG(payment_amount), 2) AS avg_payment_amount_2_decimal
, ROUND(AVG(payment_amount), 4) AS avg_payment_amount_4_decimal
FROM getting_started.payments
;
Calculating the smallest Payment Amount:
SELECT
MIN(payment_amount) AS min_payment_amount
FROM getting_started.payments
;
Calculating the largest Payment Amount:
SELECT
MAX(payment_amount) AS max_payment_amount
FROM getting_started.payments
;
Counting the total number of Payment Amount values:
SELECT
COUNT(payment_amount) AS count_payment_amount
FROM getting_started.payments
;
Counting the total number of unique/distinct Payment Amount values (if this is the same as above, then all values are unique/distinct):
SELECT
COUNT(DISTINCT payment_amount) AS count_unique_payment_amount
FROM getting_started.payments
;
Putting them all together in one query:
SELECT
SUM(payment_amount) AS total_payment_amount
, AVG(payment_amount) AS avg_payment_amount
, MIN(payment_amount) AS min_payment_amount
, MAX(payment_amount) AS max_payment_amount
, COUNT(payment_amount) AS count_payment_amount
, COUNT(DISTINCT payment_amount) AS unique_count_payment_amount
FROM getting_started.payments
;