--%>

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 : Explain Mutator method Mutator method :

    Mutator method: It is a method specifically designed to permit controlled modification of a private attribute of a class. By convention, we name the mutators with a set prefix obeyed by the name of the attribute being transformed. For example, the mut

  • Q : Define the term Class constant Define

    Define the term Class constant: A variable which is defined as both final and static.

  • Q : What is Bookmark Bookmark : It is

    Bookmark: It is employed by a Web browser to memorize details of a Uniform Resource Locator (URL).

  • Q : Software Quality Assurance and Software

    What real-life experience have you gained along with Software Quality Assurance as well as Software Quality Control?

  • Q : Write a recursive implementation of

    Assignment 5 Selecting Array Elements Implement the following C++ code in assembly language, using the block-structured .IF and .WHILE directives. Assume that all variables are 32-bit signed integers: int array[] = {10,60,20,33,72,89,45,65,72,18}; int sample = 50; intArraySize = s

  • Q : Describe Method result Method result :

    Method result: The value returned from a method through a return statement. The kind of expression in the return statement should match the return type declared in method header.

  • 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 : How class can be prevented from

    How class can be prevented from inheriting further?

  • Q : Maintaining audit trail of product

    The accuracy of product table data is crucial and the Brwebean’s. owner prefers to have an audit file which contains information regarding all DML activity on the BB_PRODUCT table. This information must point out the user id of the user running a DML statement,

  • Q : What is Hash function Hash function : A

    Hash function: A function employed to generate a hash code from the random contents of an object. The classes can override the hash Value method, inherited from the Object class, to state their own hash function.