Function to Calculate Total Order Amount
Function to Calculate Total Order Amount
OrderDetails Table:
Query Explanation:
● DELIMITER //
Temporarily changes the delimiter so that we can write multiple statements within our function without conflicts.
● CREATE FUNCTION GetTotalAmount(P_orderID INT)
Defines a function named GetTotalAmount that accepts one parameter, P_orderID, which is an integer.
● RETURNS DECIMAL(10,2)
Specifies that the function will return a decimal number with 2 decimal places.
● DETERMINISTIC
Indicates that the function always produces the same result for the same input, which helps MySQL with optimizations.
● DECLARE TotalAmount DECIMAL(10,2);
Declares a local variable TotalAmount to store the calculated total amount.
● SELECT SUM((UnitPrice * Quantity) - Discount) INTO TotalAmount FROM orderdetails WHERE OrderID = P_orderID;
Uses the SELECT ... INTO syntax to calculate the total amount (by multiplying UnitPrice by Quantity and then subtracting Discount) for the given OrderID from the orderdetails table, and stores the result in the variable TotalAmount. - Note: Make sure that the column names (Quantity and Discount) match exactly with those in your table.
● RETURN TotalAmount;
Returns the calculated total amount.
● DELIMITER ;
Resets the delimiter to the default semicolon (;).
SQL Query:
USE SalesInventoryDB;
DELIMITER //
CREATE FUNCTION GetTotalAmount(P_orderID INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE TotalAmount DECIMAL(10,2);
-- Calculate and store the total amount into the variable TotalAmount.
SELECT SUM((UnitPrice * Quantity) - Discount)
INTO TotalAmount
FROM orderdetails
WHERE OrderID = P_orderID;
RETURN TotalAmount;
END //
DELIMITER ;
-- This query calls the function GetTotalAmount with an OrderID of 3.
-- The function calculates and returns the total amount for that order.
-- The result is aliased as TotalOrderAmount for clear output labeling.
SELECT GetTotalAmount(3) AS TotalOrderAmount;
SELECT GetTotalAmount(3) AS TotalOrderAmount;
Output:
