sqlbeginner10 minutes

Write a SQL Function to Calculate Total Sales by Product

Create a SQL function that calculates the total sales amount for a given product ID from a sales table.

Challenge prompt

Write a SQL function named get_total_sales that accepts a product_id (integer) as input and returns the total sales amount (sum of quantity * price) for that product from the sales table. The sales table schema is: sales(sale_id INT, product_id INT, quantity INT, price DECIMAL). If the product has no sales, the function should return 0.

Guidance

• Use SUM() aggregation to calculate total sales.
• Handle the case where no rows exist for the given product by returning 0 instead of NULL.

Hints

• Consider using COALESCE or IFNULL to handle NULL results from SUM().
• Make sure your function accepts an input parameter for product_id.
• Use a simple SELECT statement inside the function to get the sum.

Starter code

CREATE OR REPLACE FUNCTION get_total_sales(prod_id INT) RETURNS DECIMAL AS $$
BEGIN
  -- Your code here
END;
$$ LANGUAGE plpgsql;

Expected output

For product_id = 101, if the sales are (2 units at $10) and (3 units at $15), expected output is 65.0

Core concepts

SQL functionsaggregation with SUMhandling NULL with COALESCE

Challenge a Friend

Send this duel to someone else and see if they can solve it.