179|SQL – Group By for Greatness

[Nate_Hansen]

Time to complete: 25 min
Rating: Intermediate
Comments: Good stretch

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 od.inventory_id = i.inventory_id
   GROUP BY i.inventory_name;

3. 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

[Gavin_Ross]

Time to complete: 15 mins
Difficulty: Beginner
Solutions:

1. 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;

2. 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;

3. 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;

[Ian_Bonine]

time to complete: 20 minutes
rating: intermediate

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 (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 i.inventory_name;

3,
SELECT Vendor.company_name, Sum(orderdetail.qty_ordered*orderdetail.price_per_unit) AS Expr1

FROM Vendor AS Vendor_1 INNER JOIN (Vendor INNER JOIN (Orders INNER JOIN OrderDetail ON Orders.order_id = OrderDetail.order_id) ON Vendor.vendor_id = Orders.vendor_id) ON Vendor_1.vendor_id = Orders.vendor_id

GROUP BY Vendor.company_name;

[Connor_Smith]

Time to Complete: 15 minutes
Difficulty: Easy

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_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;

[Andrew_Wells]

Time to complete: 25 minutes
Rating: Intermediate
Solutions:
1.
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
2.
SELECT Inventory.inventory_name, ROUND(AVG(OrderDetail.qty_ordered),2) AS avg_qty_ordered
FROM Inventory
INNER JOIN OrderDetail ON OrderDetail.inventory_id = Inventory.inventory_id
GROUP BY Inventory.inventory_name
3.
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

[Katelyn_Hamilton]

Time to complete: 20 minutes
Difficulty: Easy
Solution:

1. 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;
2. 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;
3. 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;

[Katie_Kim]

Time to complete: 20 Minutes
Difficulty: Medium
Solutions:

1. 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;
2. SELECT i.inventory_name, ROUND(AVG(od.qty_ordered), 2) AS avg_qty_ordered FROM Inventory i
   INNER JOIN OrderDetail 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) 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;

[Tyson_Yee]

Time to Complete: 15 minutes
Rating: Beginner to moderate
Solutions:

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
   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;

[Chris_Sosa]

Time to Complete: 10 minutes
Rating: Beginner

1. SELECT e.first_name, e.last_name, COUNT(o.emp_order_creation_id) AS Count_of_Orders
   FROM Employee AS e
   LEFT 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(qty_ordered),2) AS avg_aty_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 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

[Molly_Pratt]

Time to Complete: 15
Difficulty: Easy
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;

[Rachel_Lopez]

Time to Complete: 15 minutes
Rating: Easy
Comments: took me a while to figure out the nesting of the joins, but other than that this was a fast walk through

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, avg(OrderDetail.qty_ordered) 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;

[Hailey_Smith]

Time: 15 minutes
Difficulty: Intermediate

1: 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 first_name, last_name;

2: 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;

3: SELECT v.company_name, SUM(od.qty_ordered * od.price_per_unit) AS TotalPayable
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;

[Lilia_Fisher]

Time to complete: 15 minutes
Rating: Intermediate
I liked the challenge.

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;

[Amanda_Garton]

1.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;
2. SELECT inventory_name, ROUND(AVG(qty_ordered), 2) AS avg_qty_ordered
FROM Inventory
INNER JOIN OrderDetail ON inventory.inventory_id = OrderDetail.inventory_id
GROUP BY inventory_name
3. SELECT company_name, SUM(qty_ordered * 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 company_name

[Kate_Munger]

Time to Complete: 12 minutes
Difficulty: Intermediate

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 Count_of_Orders
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;

[Austin_Smith]

1. 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 o.emp_order_creation_id = e.employee_id
   group by first_name, last_name
2. 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
3. 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

[Aaron_Greenwood]

Time to complete: 15 mins
Difficulty: 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(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 total_payable_amount
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;

[Spencer_McArthur]

TTC: 20
Rating:intermediate
Solutions
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(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;

q3
SELECT v.company_name,
SUM(od.qty_ordered * od.price_per_unit) AS Amount_Outstanding
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;

[Justin_Day]

Time to Complete: 10 minutes
Difficulty: Easy
Solution: See Below

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 first_name, 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;

[Tyler_Herget]

Time to Complete: 20 Minutes
Difficulty: Moderate
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 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(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;