SQL Queries Using Aggregate Functions – II

SQL is a standard language for storing, manipulating and retrieving data in databases.
In this series of posts, we will discuss the SQL queries from very Basic to Advanced.
It is recommended to view the post in the following order:
1. SQL Basic Queries Example – I
2. SQL Basic Queries Example – II
3. SQL Queries Using Aggregate Functions – I
4. SQL Queries Using Aggregate Functions – II
5. SQL Intermediate Queries Example – I
6. SQL Intermediate Queries Example – II
7. SQL Advanced Queries Example – I
8. SQL Advanced Queries Example – II

In this post, we will look at the SQL queries using Aggregate Functions.

Create the tables described below:

Table Name: sales

+---------+------------+------------+---------------+--------------+
 | orderID | orderDate  | orderPrice | orderQuantity | customerName |
 +---------+------------+------------+---------------+--------------+
 |       1 | 2005/12/22 |        160 |             2 | Smith        |
 |       2 | 2005/08/10 |        190 |             2 | Johnson      |
 |       3 | 2005/07/13 |        500 |             5 | Baldwin      |
 |       4 | 2005/07/15 |        420 |             2 | Smith        |
 |       5 | 2005/12/22 |       1000 |             4 | Wood         |
 |       6 | 2005/10/02 |        820 |             4 | Smith        |
 |       7 | 2005/11/03 |       2000 |             2 | Baldwin      |
 +---------+------------+------------+---------------+--------------+

Command:

 create table sales(
 orderID int,
 orderDate varchar(20),
 orderPrice float,
 orderQuantity int,
 customerName varchar(20)
 );


 insert into sales values(1, '2005/12/22', 160, 2, 'Smith');
 insert into sales values(2, '2005/08/10', 190, 2, 'Johnson');
 insert into sales values(3, '2005/07/13', 500, 5, 'Baldwin');
 insert into sales values(4, '2005/07/15', 420, 2, 'Smith');
 insert into sales values(5, '2005/12/22', 1000, 4, 'Wood'); insert into sales values(6, '2005/10/02', 820, 4, 'Smith');
 insert into sales values(7, '2005/11/03', 2000, 2, 'Baldwin');

Exercise on Aggregate Functions

a) Count how many orders have made a customer with CustomerName of Smith.

Command:
 select count(*),customerName from sales where customerName = 'Smith';


 Output:
 +----------+--------------+
 | count(*) | customerName |
 +----------+--------------+
 |        3 | Smith        |
 +----------+--------------+

b) Find the number of unique customers that have ordered from the store.

 Command:
 select count(distinct customerName) from sales;


 Output:
 +------------------------------+
 | count(distinct customerName) |
 +------------------------------+
 |                            4 |
 +------------------------------+

c) Find out the total number of items ordered by all the customers.

Command:
 select sum(orderQuantity) from sales;


 Output:
 +--------------------+
 | sum(orderQuantity) |
 +--------------------+
 |                 21 |
 +--------------------+

d) Find out the average number of items per order.

Command:
 select avg(orderQuantity) from sales;


 Output:
 +--------------------+
 | avg(orderQuantity) |
 +--------------------+
 |             3.0000 |
 +--------------------+

e) Find out the average OrderQuantity for all orders with OrderPrice greater than 200.

Command:
 select avg(orderQuantity) from sales where orderPrice > 200;


 Output:
 +--------------------+
 | avg(orderQuantity) |
 +--------------------+
 |             3.4000 |
 +--------------------+

f) Find out what was the minimum price paid for any of the orders.

Command:
 select min(orderPrice) from sales;


 Output:
 +-----------------+
 | min(orderPrice) |
 +-----------------+
 |             160 |
 +-----------------+

g) Find out the highest OrderPrice form the given sales table.

Command:
 select max(orderPrice) from sales; 


 Output:
 +-----------------+
 | max(orderPrice) |
 +-----------------+
 |            2000 |
 +-----------------+

h) List out unique customers’ names only from the table.

Command:
 select distinct customerName from sales;


 Output:
 +--------------+
 | customerName |
 +--------------+
 | Smith        |
 | Johnson      |
 | Baldwin      |
 | Wood         |
 +--------------+

i) List the name of the customers who have given orders in the month of December.

Command:
 select customerName from sales where orderDate like '%/12/%';


 Output:
 +--------------+
 | customerName |
 +--------------+
 | Smith        |
 | Wood         |
 +--------------+

j) Find out the total amount of money spent on each of the customers.

Command:
 select sum(orderPrice), customerName from sales group by customerName;


 Output:
 +-----------------+--------------+
 | sum(orderPrice) | customerName |
 +-----------------+--------------+
 |            2500 | Baldwin      |
 |             190 | Johnson      |
 |            1400 | Smith        |
 |            1000 | Wood         |
 +-----------------+--------------+

k) Select all unique customers who have spent more than 1200 in the store.

Command:
 select CustomerName from sales group by CustomerName having sum(OrderPrice) > 1200;


 Output:
 +--------------+
 | customerName |
 +--------------+
 | Baldwin      |
 | Smith        |
 +--------------+

l) Select all customers that have ordered more than 5 items in total from all their orders.

Command:
  select customerName, sum(orderQuantity) as sum from sales group by customerName having sum > 5;


 Output:
 +--------------+------+
 | customerName | sum  |
 +--------------+------+
 | Baldwin      |    7 |
 | Smith        |    8 |
 +--------------+------+

m) Select all customers who have spent more than 1000, after 10/01/2005.

Command:
 select * from sales where orderDate > '2005/10/01' group by customerName having sum(orderPrice) > 1000;


Output:
 +---------+------------+------------+---------------+--------------+
 | orderID | orderDate  | orderPrice | orderQuantity | customerName |
 +---------+------------+------------+---------------+--------------+
 |       7 | 2005/11/03 |       2000 |             2 | Baldwin      |
 +---------+------------+------------+---------------+--------------+

n) Select orders in increasing order of order price.

Command:
 select * from sales order by orderPrice asc;


Output:
 +---------+------------+------------+---------------+--------------+
 | orderID | orderDate  | orderPrice | orderQuantity | customerName |
 +---------+------------+------------+---------------+--------------+
 |       1 | 2005/12/22 |        160 |             2 | Smith        |
 |       2 | 2005/08/10 |        190 |             2 | Johnson      |
 |       4 | 2005/07/15 |        420 |             2 | Smith        |
 |       3 | 2005/07/13 |        500 |             5 | Baldwin      |
 |       6 | 2005/10/02 |        820 |             4 | Smith        |
 |       5 | 2005/12/22 |       1000 |             4 | Wood         |
 |       7 | 2005/11/03 |       2000 |             2 | Baldwin      |
 +---------+------------+------------+---------------+--------------+

o) Select orders in decreasing order of order price.

Command:
 select * from sales order by orderPrice desc;  


Output:
 +---------+------------+------------+---------------+--------------+
 | orderID | orderDate  | orderPrice | orderQuantity | customerName |
 +---------+------------+------------+---------------+--------------+
 |       7 | 2005/11/03 |       2000 |             2 | Baldwin      |
 |       5 | 2005/12/22 |       1000 |             4 | Wood         |
 |       6 | 2005/10/02 |        820 |             4 | Smith        |
 |       3 | 2005/07/13 |        500 |             5 | Baldwin      |
 |       4 | 2005/07/15 |        420 |             2 | Smith        |
 |       2 | 2005/08/10 |        190 |             2 | Johnson      |
 |       1 | 2005/12/22 |        160 |             2 | Smith        |
 +---------+------------+------------+---------------+--------------+

Related Posts

Leave a Reply

Download