GROUP BY statements use various aggregate functions to combine rows and look at aggregated information.

Aggregate functions used below: MIN, MAX, AVG, COUNT, SUM.

The calls below are made a hypothetical transaction database with columns first_name, last_name, customer_id, employee_id, amount, and transaction_quality.

GROUP BY with SUM
Return the total amount paid by each customer:

SELECT customer_id, SUM(amount)
FROM transaction
GROUP BY customer_id;

GROUP BY with COUNT

Return the number of transactions by transaction_quality:

SELECT transaction_quality, COUNT(transaction_quality)
FROM transaction
GROUP BY transaction_quality;

GROUP BY with AVG and ROUND

Return the average purchase price (rounded to 2 decimal places) for each customer:

SELECT customer_id, ROUND(AVG(amount),2)
FROM transaction
ORDER BY customer_id;

GROUP BY with SUM and ORDER BY

Return the five employees who have sold the most (by amount rather than quantity of transactions):

SELECT employee_id, SUM(amount)
FROM transaction
GROUP BY employeee_id
ORDER BY SUM(amount) DESC
LIMIT 5;

 

Categories: SQL

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts

SQL

UNION

This command combines results from two or more select queries. In order to use the UNION command, you must have the same number of columns in each select statement and the corresponding columns must have Read more…

SQL

HAVING

This command is used with GROUP BY to filter group rows. It is similar to WHERE, but HAVING applies to the aggregated rows after the GROUP BY command (WHERE applies to individual rows before the Read more…

SQL

Basic SQL Statements

SQL is case-insensitive, but by convention SQL commands are typed in upper case. The examples below return specific data from a hypothetical database named transactions with columns: first_name, last_name, customer_id, and price. Basic commands: SELECT Read more…