--%>

Compute the Total Shopper Spending in SQL

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.

E

Expert

Verified

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;

   Related Questions in Programming Languages

  • Q : Define Swapping Swapping : An

    Swapping: An {operating system} is frequently able to run programs which need more memory than is physically accessible on the host system. In order to do this, the complete memory needed is broken down into smaller pieces that are swapped in whenever

  • Q : Define several features of XQuery

    Define several features of XQuery?

  • Q : Explain Branch instruction Branch

    Branch instruction: It stores a new instruction address into the program counter. The consequence of this is the next instruction to be fetched will not generally be the one instantly following the branch instruction. Therefore the normal chronologica

  • Q : Reference variables Reference variable

    Q : Components of ASP.NET AJAX architecture

    Name the components of ASP.NET AJAX architecture?

  • Q : How virtual machine simplify writing

    How does a virtual machine simplify the task of writing a distributed application?

  • Q : What is Quotient Quotient: Whenever

    Quotient: Whenever integer division is executed, the outcome comprises of a quotient and a remainder. The quotient symbolizes the integer number of times which the divisor divides into the dividend. For example, in 5/3, 5 is the dividend and 3 is the

  • Q : Define the term Direct recursion Define

    Define the term Direct recursion: Recursion which outcomes from a method of calling itself.

  • Q : Non linear dynamic model equation to

    Could you please show the steps from non linear dynamic model equation to linear equation?

  • Q : Explain Out-of-bounds value

    Out-of-bounds value: It is a redundant value employed to point out that a different action from the norm is needed at some point. The read technique of InputStream returns -1 to point out that the end of a stream has been reached, for illustration, ra