179|SQL – Group By for Greatness

Marta_Ellsworth · April 30, 2024, 8:16pm

BYU Student Author: @Marta_Ellsworth
Reviewers: @Andrew_Wilson, @Sterling_Lane
Estimated Time to Solve: 25 Minutes

This is an intro challenge that is part of the SQL Learning Path.

We provide the solution to this challenge using:

SQL

Need a program? Click here.

Overview
You are an employee in the accounting department at Precision Plane Parts. You’ve been asked to present some company data at this week’s catch-up meeting with all the department heads. You’ve decided to use SQL to ensure your data is accurate in case anyone has any questions. Follow the instructions to retrieve each report, so you can make a good impression on all the executives.

Instructions

How many orders has each employee created? Only include employees who have created at least one order.
a. Select the employee “first_name” and “last_name” from the “Employee” table.
b. Select the count of the “emp_order_creation_id” from the “Orders” table and rename it to “Count_of_Orders”.
c. Join the “Employee” table with the “Orders” table.
d. Group by the employee “first_name” and “last_name”.
What is the average quantity ordered for each inventory item?
a. Select the “inventory_name” from the “Inventory” table.
b. Select the average of the “qty_ordered” from the “OrderDetail” table. Round the average to two decimal places and rename the column to “avg_qty_ordered”.
c. Join the Inventory and OrderDetail tables.
d. Group by the “inventory_name”.
What is the total payable amount for each vendor based on our outstanding orders?
a. Select the “company_name” from the “Vendor” table.
b. Multiply “qty_ordered” with “price_per_unit” from the “OrderDetail” table and then sum them.
c. Join the “Vendor” table and the “Orders” table. Then join the “OrderDetail” to both tables.
d. Group by the “company_name”.

Data Files

Challenge179_Data.accdb

Suggestions and Hints

To rename a column use AS in the SELECT statement (Count(o.emp_order_creation_id) AS Count_of_Orders).
When joining the “Employee” and “Orders” table use aliases for your tables (SELECT e.employee_id FROM Employee AS e (The e is the alias for the employee table.)).
Make sure you include all non-aggregate columns in the GROUP BY statement if you have aggregations in your SELECT statement.

Solution

Solution Code

Query 1

SELECT e.first_name, e.last_name, COUNT(o.emp_order_creation_id) AS Count_of_Orders 
FROM Employee AS e  
INNER JOIN Orders AS o ON e.employee_id = o.emp_order_creation_id 
GROUP BY e.first_name, e.last_name;

Query 2

SELECT i.inventory_name, ROUND(AVG(od.qty_ordered), 2) AS avg_qty_ordered 
FROM Inventory AS i  
INNER JOIN OrderDetail AS od ON i.inventory_id = od.inventory_id 
GROUP BY i.inventory_name;

Query 3

SELECT v.company_name, SUM(qty_ordered * price_per_unit) AS Total_Payable 
FROM (Vendor AS v  
INNER JOIN Orders AS o ON v.vendor_id = o.vendor_id) 
INNER JOIN OrderDetail AS od ON o.order_id = od.order_id 
GROUP BY v.company_name;

Challenge179_Solution.accdb
Solution Video: Challenge 179|SQL – Group By for Greatness

Kaylie_Sweeting · June 28, 2024, 8:31pm

Time to complete: 25 minutes
Rating: Introductory

I enjoyed this challenge! I don’t have much experience with the INNER JOIN function, so it was great practice. Because I had syntax issues, I followed the video to achieve the solution.

Gabrielle_Johnson · February 20, 2025, 8:20pm

Time to Complete: 12 minutes
Difficulty: Easy
Solution: See Below
1:
SELECT e.first_name, e.last_name, count(o.emp_order_creation_id) AS Count_of_Orders
FROM Employee as e
INNER JOIN Orders as o ON e.employee_id = o.emp_order_creation_id
GROUP BY e.first_name, e.last_name;
2:
SELECT i.inventory_name, round(Avg(od.qty_ordered),2) AS avg_qty_ordered
FROM Inventory AS i
INNER JOIN OrderDetail AS od ON i.inventory_id = od.inventory_id
GROUP BY i.inventory_name;
3:
SELECT v.company_name, sum(od.qty_ordered*od.price_per_unit) AS Total_Payable_Amount
FROM (Vendor as v INNER JOIN Orders as o ON v.vendor_id = o.vendor_id)
INNER JOIN OrderDetail as od ON o.order_id = od.order_id
GROUP BY v.company_name;

Porter_Rich · February 20, 2025, 8:57pm

Time to Complete: 10 Mins
Rating: Beginner
Solutions:

1.SELECT e.first_name, e.last_name, COUNT(o.emp_order_creation_id) AS Count_of_Orders
FROM Orders AS o INNER JOIN Employee AS e ON o.emp_order_creation_id = e.employee_id
GROUP BY e.first_name, e.last_name;

SELECT i.inventory_name, ROUND(AVG(od.qty_ordered),2) AS avg_qty_ordered
FROM Inventory AS i INNER JOIN OrderDetail AS od ON i.inventory_id = od.inventory_id
GROUP BY i.inventory_name;
SELECT v.company_name, SUM(od.qty_ordered*od.price_per_unit) AS Amount_Payable
FROM ((Vendor AS v INNER JOIN Orders as o ON v.vendor_id = o.vendor_id) INNER JOIN OrderDetail AS od ON o.order_id = od.order_id)
GROUP BY v.company_name;

Meadow_Mitchell · February 20, 2025, 9:27pm

Time to complete: 12 minutes
Difficulty: Beginner
Notes: Enjoyed the challenge!

SELECT e.first_name, e.last_name, COUNT(o.emp_order_creation_id) AS Count_of_Orders
FROM Employee AS e
INNER JOIN Orders AS o ON e.employee_id = o.emp_order_creation_id
GROUP BY e.first_name, e.last_name;
SELECT i.inventory_name, ROUND(AVG(od.qty_ordered), 2) AS avg_qty_ordered
FROM Inventory AS i
INNER JOIN OrderDetail AS od ON i.inventory_id = od.inventory_id
GROUP BY i.inventory_name;
SELECT v.company_name, SUM(qty_ordered * price_per_unit) AS Total_Payable
FROM (Vendor AS v INNER JOIN Orders AS o ON o.vendor_id = v.vendor_id)
INNER JOIN OrderDetail AS od ON od.order_id = o.order_id
GROUP BY v.company_name;

Madelyn_Boulton · February 20, 2025, 11:39pm

Time to complete: 15 minutes
Difficulty: Easy/Medium
Solution:

SELECT e.first_name, e.last_name, COUNT(o.emp_order_creation_id) AS Count_of_Orders
FROM Employee e
INNER JOIN Orders o ON e.employee_id=o.emp_order_creation_id
GROUP BY e.first_name, e.last_name;
SELECT i.inventory_name, ROUND(AVG(od.qty_ordered),2) AS avg_qty_ordered
FROM Inventory i
INNER JOIN OrderDetail od ON i.inventory_id=od.inventory_id
GROUP BY i.inventory_name;
SELECT v.company_name, SUM(od.qty_ordered*od.price_per_unit)
FROM (Vendor v
INNER JOIN Orders o ON v.vendor_id=o.vendor_id)
INNER JOIN OrderDetail od ON o.order_id=od.order_id
GROUP BY v.company_name;

ShortyoShire · February 21, 2025, 3:01am

Time to Complete: 16 minutes
Difficulty: Easy
Comments: I didn’t realize you could round the average inside like that
Solution:
1:SELECT e.first_name, last_name, COUNT(o.emp_order_creation_id) AS Count_of_Orders
FROM Employee AS e
INNER JOIN Orders AS o ON o.emp_order_creation_id=e.employee_id
GROUP BY e.first_name, e.last_name;
2:SELECT i.inventory_name, ROUND(AVG(od.qty_ordered),2) AS avg_qty_ordered
FROM Inventory AS i
INNER JOIN OrderDetail AS od ON od.inventory_id=i.inventory_id
GROUP BY i.inventory_name;
3:SELECT v.company_name, SUM(qty_ordered * price_per_unit) AS Total_Payable
FROM (Vendor AS v
INNER JOIN Orders AS o ON v.vendor_id = o.vendor_id)
INNER JOIN OrderDetail AS od ON o.order_id = od.order_id
GROUP BY v.company_name;

Kathryn_Thompson_Whi · February 21, 2025, 6:24pm

Time:13 mins
Difficulty: Easy
1.
SELECT e.first_name, e.last_name, COUNT(o.emp_order_creation_id) AS Count_of_Orders
FROM Employee AS e
INNER JOIN Orders AS o ON e.employee_id=o.emp_order_creation_id
GROUP BY e.first_name, e.last_name;
2.
SELECT i.inventory_name, round(Avg(od.qty_ordered),2) AS avg_qty_ordered
FROM Inventory AS i
INNER JOIN OrderDetail AS od ON i.inventory_id = od.inventory_id
GROUP BY i.inventory_name;
3.
SELECT v.company_name, sum(od.qty_ordered*od.price_per_unit) AS Total_Payable_Amount
FROM (Vendor as v INNER JOIN Orders as o ON v.vendor_id = o.vendor_id)
INNER JOIN OrderDetail as od ON o.order_id = od.order_id
GROUP BY v.company_name;

Joshua_Anderson · February 22, 2025, 5:45pm

SELECT e.first_name, e.last_name, COUNT(o.emp_order_creation_id) AS Count_of_Orders
FROM Employee AS e
INNER JOIN Orders AS o ON e.employee_id = o.emp_order_creation_id
GROUP BY e.first_name, e.last_name;

SELECT i.inventory_name, ROUND(AVG(od.qty_ordered),2) AS Avg_Quantity_Ordered
FROM Inventory AS i
INNER JOIN OrderDetail AS od ON i.inventory_id = od.inventory_id
GROUP BY i.inventory_name;

SELECT v.company_name, SUM(od.qty_ordered * od.price_per_unit) AS Total_Payable
FROM (Vendor AS v INNER JOIN Orders AS o ON v.vendor_id = o.vendor_id)
INNER JOIN OrderDetail AS od ON o.order_id = od.order_id
GROUP BY v.company_name;

Emily_Cook · February 22, 2025, 6:01pm

Time to Complete: 15 minutes
Difficulty: Easy

SELECT e.first_name, e.last_name, COUNT(o.emp_order_creation_id) AS Count_of_Orders
FROM Employee e
INNER JOIN Orders o on e.employee_id=o.emp_order_creation_id
GROUP BY e.first_name, e.last_name
SELECT i.inventory_name, ROUND(AVG(od.qty_ordered), 2) AS avg_qty_ordered
FROM Inventory i INNER JOIN OrderDetail od ON i.inventory_id=od.inventory_id
GROUP BY inventory_name
SELECT v.company_name, SUM(od.qty_ordered*od.price_per_unit) AS Total_Payable
FROM (Vendor v INNER JOIN Orders o ON v.vendor_id=o.vendor_id)
INNER JOIN OrderDetail od ON o.order_id=od.order_id
GROUP BY v.company_name

Nolan_Brewer · February 22, 2025, 6:50pm

Time to Complete: 10 minutes
Difficulty: Easy

Q1
SELECT e.first_name, e.last_name, Count(o.emp_order_creation_id) as “Count_of_Orders”
FROM Employee e
INNER JOIN Orders o On e.employee_id = o.emp_order_creation_id
Group by e.first_name, e.last_name;
Q2
SELECT i.inventory_name, ROUND(AVG(od.qty_ordered), 2) AS avg_qty_ordered
FROM Inventory i
INNER JOIN OrderDetail od ON i.inventory_id = od.inventory_id
GROUP BY i.inventory_name;
Q3
SELECT v.company_name, SUM(od.qty_ordered*od.price_per_unit) as “amount due”
FROM (Vendor v
INNER JOIN Orders o on v.vendor_id = o.vendor_id)
INNER JOIN OrderDetail od on o.order_id = od.order_id
GROUP BY v.company_name;

Miles_Maynes · February 22, 2025, 7:26pm

Time to complete: 15 minutes
Difficulty: Easy
Solution:

SELECT e.first_name, e.last_name, COUNT(o.emp_order_creation_id) AS Count_of_Orders
From [Employee] AS e
INNER JOIN [Orders] AS o on e.employee_id = o.emp_order_creation_id
GROUP BY e.first_name, e.last_name
SELECT i.inventory_name, round(avg(od.qty_ordered), 2) AS avg_qty_ordered
FROM [Inventory] AS i
INNER JOIN [OrderDetail] AS od ON i.inventory_id = od.inventory_id
GROUP BY inventory_name

3.SELECT v.company_name, sum((od.qty_ordered*od.price_per_unit))
FROM ([Vendor] AS v
LEFT JOIN [Orders] AS o on v.vendor_id = o.vendor_id)
LEFT JOIN [OrderDetail] AS od ON o.order_id = od.order_id
GROUP BY v.company_name

Lucas_Argente · February 22, 2025, 7:51pm

Time to complete: 15 minutes
Difficulty: Easy
Solution:
Q1:
SELECT employee.first_name, employee.last_name, COUNT(Orders.emp_order_creation_id) AS Count_of_Orders

FROM (Employee

INNER JOIN Orders ON Employee.employee_id=Orders.emp_order_creation_id)

GROUP BY Employee.first_name, Employee.last_name;

Q2:
SELECT Inventory.inventory_name, ROUND(AVG(OrderDetail.qty_ordered),2) AS avg_qty_ordered

FROM (Inventory

INNER JOIN OrderDetail ON Inventory.inventory_id=OrderDetail.inventory_id)

GROUP BY Inventory.inventory_name;

Q3:
SELECT Vendor.company_name, SUM(OrderDetail.qty_ordered * OrderDetail.price_per_unit) AS total_payable

FROM (Vendor

INNER JOIN Orders ON Vendor.vendor_id=Orders.vendor_id)

INNER JOIN OrderDetail ON Orders.order_id=OrderDetail.order_id

GROUP BY Vendor.company_name;

Cooper_Wise · February 22, 2025, 11:45pm

Time to complete: 15 min
Difficulty: Intermediate
Solution:
1.
SELECT e.first_name, e.last_name, COUNT(o.emp_order_creation_id) AS count_of_orders
FROM Employee AS e
INNER JOIN orders as o
ON e.employee_id = o.emp_order_creation_id
GROUP BY e.first_name, e.last_name;
2.
SELECT i.inventory_name, ROUND(AVG(od.qty_ordered), 2) AS avg_qty_ordered
FROM Inventory AS i
INNER JOIN OrderDetail AS od
ON i.inventory_id = od.inventory_id
GROUP BY i.inventory_name;
3.
SELECT v.company_name, sum(od.qty_ordered*od.price_per_unit) AS Total_Payable_Amount
FROM (Vendor as v INNER JOIN Orders as o
ON v.vendor_id = o.vendor_id)
INNER JOIN OrderDetail as od
ON o.order_id = od.order_id
GROUP BY v.company_name;

Henry_Chen · February 23, 2025, 9:44am

Time :20 mins

Level: Intermidiate

SELECT e.first_name,e.last_name, COUNT(o.emp_order_creation_id) AS Count_of_Orders
FROM Employee AS e
INNER JOIN Orders o ON o.emp_order_creation_id=e.employee_id
GROUP BY e.first_name,e.last_name;
SELECT I.inventory_name, ROUND(AVG(od.qty_ordered), 2) AS avg_qty_ordered
FROM Inventory AS I
INNER JOIN OrderDetail AS od ON od.inventory_id = I.inventory_id
GROUP BY I.inventory_name;

3.SELECT V.company_name, SUM(OD.qty_ordered*OD.price_per_unit)

FROM(( Vendor AS V

INNER JOIN Orders AS O ON V.vendor_id=O.vendor_id)
INNER JOIN OrderDetail AS OD ON OD.order_id=O.order_id
)
GROUP BY V.company_name;

Shane_Hawkins · February 23, 2025, 11:22pm

Time to Complete: 15 minutes
Difficulty: Easy
Solution: See Below
1: SELECT e.first_name, e.last_name,count( o.emp_order_creation_id) as Count_of_Orders
from orders as O
inner join Employee as e on o.emp_order_creation_id=e.employee_id
group by e.first_name, e.last_name
2: SELECT i.inventory_name, round(Avg(od.qty_ordered),2) as avg_qty_ordered
from Inventory as i
inner join OrderDetail as od on i.inventory_id=od.inventory_id
group by i.inventory_name;
3: SELECT v.company_name, sum(od.qty_ordered*od.price_per_unit) as Total_Payable_Amount
from (Vendor as v
inner join orders as o on v.vendor_id=o.vendor_id)
inner join OrderDetail as od on o.order_id=od.order_id
group by v.company_name

McKenna_Hall · February 24, 2025, 1:13am

Time to complete: 20 minutes
Rating: Intermediate
Notes: An explanation in the solution of why we should use inner join vs. left join, etc. would be helpful.
Solution:

SELECT e.first_name, e.last_name, COUNT(o.emp_order_creation_id) AS Count_of_Orders
FROM Employee AS e
INNER JOIN Orders AS o ON e.employee_id = o.emp_order_creation_id
GROUP BY e.first_name, e.last_name;
SELECT i.inventory_name, ROUND(AVG(od.qty_ordered) , 2) AS avg_qty_ordered
FROM Inventory AS i
INNER JOIN OrderDetail AS od ON i.inventory_id = od.inventory_id
GROUP BY i.inventory_name;
SELECT v.company_name, SUM((od.qty_ordered)*(od.price_per_unit)) AS Total_Payable
FROM (Vendor AS v
INNER JOIN Orders AS o ON v.vendor_id = o.vendor_id)
INNER JOIN OrderDetail AS od ON o.order_id = od.order_id
GROUP BY v.company_name;

Cannon_Hamilton · February 24, 2025, 2:35am

Q1: SELECT e.first_name, e.last_name, COUNT(o.emp_order_creation_id) AS Count_of_Orders
FROM Employee AS e
INNER JOIN Orders AS o ON e.employee_id=o.emp_order_creation_id
GROUP BY e.first_name, e.last_name;

Q2: SELECT i.inventory_name, ROUND(AVG(o.qty_ordered),2) AS avg_qty_ordered
FROM Inventory AS i
INNER JOIN OrderDetail AS o ON i.inventory_id=o.inventory_id
GROUP BY inventory_name;

Q3: SELECT v.company_name, SUM(od.qty_ordered*od.price_per_unit) AS payable_amt
FROM ((Vendor AS v
INNER JOIN Orders AS o ON v.vendor_id=o.vendor_id)
INNER JOIN OrderDetail AS od ON o.order_id=od.order_id)
GROUP BY v.company_name;

Meilan_Rhoton · February 24, 2025, 4:34am

Time to Complete: 15 minutes
Difficulty: Beginner / Intermediate

SELECT e.first_name, e.last_name, COUNT (o.emp_order_creation_id) AS Count_of_Orders
FROM employee AS e
INNER JOIN Orders AS o ON e.employee_id=o.emp_order_creation_id
Group by e.first_name, e.last_name
SELECT inventory.inventory_name, ROUND(Avg(od.qty_ordered),2) AS avg_qty_ordered
FROM Inventory
INNER JOIN OrderDetail AS od ON Inventory.inventory_id=od.inventory_id
GROUP BY inventory.inventory_name
SELECT v.company_name, SUM(od.qty_ordered*od.price_per_unit) AS Total_Payable
FROM( Vendor AS v
INNER JOIN Orders AS o ON v.vendor_id=o.vendor_id)
INNER JOIN OrderDetail AS od ON o.order_id=od.order_id
GROUP BY v.company_name

Rylee_Schriever · February 24, 2025, 4:54am

15 mins
easy

SELECT e.first_name, e.last_name, count(o.emp_order_creation_id) AS Count_of_Orders
FROM Employee as e
INNER JOIN Orders as o ON e.employee_id = o.emp_order_creation_id
GROUP BY e.first_name, e.last_name;
SELECT i.inventory_name, ROUND(AVG(od.qty_ordered),2) AS avg_qty_ordered
FROM Inventory i INNER JOIN OrderDetail od ON i.inventory_id=od.inventory_id
GROUP BY i.inventory_name;
SELECT v.company_name, SUM(od.qty_ordered*od.price_per_unit) AS Amount_Payable
FROM ((Vendor v INNER JOIN Orders o ON v.vendor_id=o.vendor_id) INNER JOIN OrderDetail od ON o.order_id=od.order_id)
GROUP BY v.company_name

Topic		Replies	Views
177\|SQL – Intro to Join Statements Challenges managerial , sql , intro , learningpath	323	1825	February 27, 2025
181\|SQL – Precision Plane Aggregation Adventure Challenges managerial , sql , intro , planes , having	12	191	March 13, 2025
176\|SQL – Precision Plane Data Presentation Challenges managerial , sql , intro , planes	324	1864	February 27, 2025
180\|SQL – Intro to Filtering and Ordering Data Challenges filter , sql , intro , order	320	2302	February 27, 2025
178\|SQL – Unions with Precision Challenges sql , union , intro	319	1509	February 27, 2025

179|SQL – Group By for Greatness

Related topics