sqlbeginner10 minutes
Build a SQL Function to Calculate the Total Price of an Order
Write a SQL function that takes an order ID and returns the total price of that order by summing up the prices of its items.
Challenge prompt
You have a table named OrderItems with columns: order_id, item_id, quantity, and price_per_item. Write a SQL function named calculate_order_total that takes an order_id as input and returns the total price, calculated as the sum of quantity * price_per_item for that order.
Guidance
- • Use the SUM aggregate function to calculate the total.
- • Filter the rows by the given order_id before summing.
- • Make sure to multiply quantity by price_per_item for each item.
Hints
- • Remember to filter using a WHERE clause on order_id.
- • The function should return a numeric value representing the total price.
Starter code
CREATE FUNCTION calculate_order_total(p_order_id INT) RETURNS DECIMAL(10,2) BEGIN
-- Your code here
END;Expected output
If OrderItems contains rows with order_id = 101 like (item_id=1, quantity=2, price_per_item=10.50) and (item_id=2, quantity=1, price_per_item=5.00), then SELECT calculate_order_total(101); should return 26.00
Core concepts
SQL functionsaggregate functionsbasic arithmetic in SQL
Challenge a Friend
Send this duel to someone else and see if they can solve it.