Intro
You can create a SELECT statement within a SELECT statement to make a more complex query
Sample Data
These are the tables we will use as an example:
SELECT *
FROM ar_invoices
| invoice# |
customer# |
salesperson# |
invoice_date |
invoice_amount |
amount_paid |
current_month_interest_charges |
total_interest_charges
|
| 1 | 1 | 1 | 2019-06-17 10:43:53.727 | 100.00 | 1.00 | 0.00 | 0.00
|
| 2 | 2 | 2 | 2019-05-28 10:43:53.727 | 50.00 | 0.00 | 5.00 | 10.00
|
| 3 | 3 | 2 | 2019-06-17 10:43:53.727 | 70.00 | 0.00 | 10.00 | 20.00
|
| 4 | 2 | 1 | 2019-07-07 10:43:53.727 | 80.00 | 0.00 | 0.00 | 0.00
|
SELECT *
FROM ar_salespeople
| salesperson# |
salesperson_name
|
| 1 | bob
|
| 2 | mary
|
| 3 | tim
|
SELECT *
FROM ar_customers
| customer# |
customer_name |
customer_address |
customer_province |
customer_rating
|
| 1 | bill | 123 main st. | ab | 1
|
| 2 | ed | 456 main st. | bc | 2
|
| 3 | john | 789 main st. | ab | 3
|
Nested Column
This nested SELECT acts like a column and only returns one value
SELECT salesperson#,
invoice_amount,
(SELECT AVG(invoice_amount) AS Average FROM ar_invoices) AS Average
FROM ar_invoices
| salesperson# |
invoice_amount |
Average
|
| 1 | 100.00 | 75.000000
|
| 2 | 50.00 | 75.000000
|
| 2 | 70.00 | 75.000000
|
| 1 | 80.00 | 75.000000
|
Nested Table
This nested SELECT acts like a table and can return multiple rows
SELECT salesperson#,
invoice_amount,
(SELECT AVG(invoice_amount) AS Average FROM ar_invoices) AS Average
FROM ar_invoices
| salesperson# |
invoice_amount |
Average
|
| 1 | 100.00 | 75.000000
|
| 2 | 50.00 | 75.000000
|
| 2 | 70.00 | 75.000000
|
| 1 | 80.00 | 75.000000
|
Special Operators
FILL THIS IN