Aggregate functions take multiple rows of data and aggregate, or combine, it into a single value. There are several aggregate functions that will be demonstrated:
This will return a precise value with many decimal places (“4.2006056453822965”).
ROUND allows users to round this down to a less precise, more comprehensible value (“4.20”).
GROUP BY Statements
GROUP BY divides the rows returned by the
SELECT statement into groups. For each group, an aggregate function can be applied. As examples, users can
COUNT the number of items in the groups, or
SUM the items.
GROUP BY without an aggregate function is the same as calling
DISTINCT on the column. PostgreSQL does not require an aggregate function to be included in the
SELECT clause when using
GROUP BY. Other SQL engines do.
PostgreSQL does not require users to include the column used in the
GROUP BY in the
SELECT clause. Other SQL engines do require customer_id, for example, to be included alongside the aggregate function in
Challenge: GROUP BY
We have two staff members with Staff IDs 1 and 2. We want to give a bonus to the staff member that handled the most payments. How many payments did each staff member handle? And how much was the total amount processed by each staff member?
Corporate headquarters is auditing the store. They want to know the average replacement cost of movies by rating. For example, R rated movies have an average replacement cost of $20.23.
We want to send coupons to the 5 customers who have spent the most money. We need the customer ids of the top 5 spenders.
HAVING is used in conjunction with
GROUP BY to filter group rows that do not satisfy a certain condition.
HAVING is analogous to
WHERE, except it is used with
HAVINGclause sets the condition for groups of rows created by the
GROUP BYclause after the
GROUP BYclause applies.
WHEREclause sets the condition for individual rows before the
GROUP BYclause applies.
We want to know what customers are eligible for our platinum credit card. The requirements are that the customer has at least a total of 40 transaction payments. What customers are eligible for the credit card?
When grouped by rating, what movie ratings have an average rental duration of more than 5 days?
Determine the customer IDs of customers who have spent at least $110 with the staff member who has an ID of 2.
How many films have titles that begin with the letter “J”?
What is the name of the customer who has the highest customer ID number, and whose name starts with an ‘E’, and who has an address ID that is less than 500?