Compute the Total Shopper Spending in SQL

Question

Most of the reports produced from the system compute the total dollars in purchases for a shopper. Process the following steps to build a function named TOT_PURCH_SF which accepts a shopper id as input and returns the total dollars which the shopper has spent with company. Utilize the function in a SELECT statement which shows the shopper id and total purchases for each and every shopper in a database.
 
1. Make and run a CREATE FUNCTION statement to build the TOT_PURCH_SF function. The function code requires a formal parameter for the shopper id and to sum the total column from the BB_BASKET table.

2. Form a SELECT statement employing the BB_SHOPPER table to generate a list of each shopper in the database and his/her respective totals.

Answer 1

create or replace function "TOT_PURCH_SF" (shopper_id in NUMBER)
return NUMBER is TOTAMT NUMBER;

begin

SELECT SUM(TOTAL) INTO TOTAMT FROM BB_BASKET WHERE IDSHOPPER=SHOPPER_ID GROUP BY IDSHOPPER;

dbms_output.put_line (totamt);

RETURN TOTAMT;

end;