--%>

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 Class scope Class scope :

    Class scope: Private variables stated outside the methods in a class contain class scope. They are available from all methods within a class, in spite of the order in which they are stated. The private methods too contain class scope. Variables and me

  • Q : What is the way to render XForms What

    What is the way to render XForms?

  • 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 Punctuation Define the

    Define the term Punctuation: Symbols like commas and semicolons that a compiler employs to understand the structure of the program.

  • Q : Define the term Local inner class and

    Define the term Local inner class and Local variable: Local inner class: It is an inner class stated in a method. Local variable: It is a variable defined within

  • Q : Define the term Scheduler Define the

    Define the term Scheduler: The portion of the Java Virtual Machine (abbreviated as JVM) which is responsible for managing the threads.

  • Q : Define Formal argument Formal argument

    Formal argument: The definition of a method’s argument is the part of a method header. Each and every formal argument has an associated type. Whenever a method is called, the actual argument values are copied into the analogous formal arguments.

  • Q : Which kernel objects is utilize for

    Which kernel objects is utilize for thread Synchronization on various processes? Answer: For thread synchronization on various processes kernel objects are utilize

  • Q : DOT Net namespace Explain the main

    Explain the main classes given by the .NET namespace to process the XML files.