1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

PL/SQL Multiplication

Discussion in 'General' started by Ace1, Sep 5, 2012.

  1. Ace1

    Ace1 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hello,

    I am tring to get the PL/SQL coding for multiplying two columns of a table. eg. Unit Price * Quanity to get a Total

    Can anyone help?
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Does it based on any parameter like order number or anything or you want to do it for all records in that table?
     
  3. Ace1

    Ace1 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    Below are the two tables from which the columns to be multiplied will be taken.
    The multiplication is to take place between SALEUNITPRICE (Located in Sales Table) and SAL_QUANITY (Located in SaleStock Table). This to get a Total Sales.

    Please let me know if you need anymore info. Many Thanks.

    CREATE TABLE SALE
    (SALEID VARCHAR2 (7) NOT NULL,
    SALEDATE DATE,
    SALEUNITPRICE NUMBER (4,2),
    CUSTOMERID VARCHAR2 (7),
    CONSTRAINT PKSALE PRIMARY KEY (SALEID),
    CONSTRAINT FKCUSTOMERID FOREIGN KEY (CUSTOMERID) REFERENCES CUSTOMER(CUSTOMERID) ON DELETE CASCADE);


    CREATE TABLE SALESTOCK
    (SALEID VARCHAR2 (7) NOT NULL,
    DVDNAME VARCHAR2 (50) NOT NULL,
    SAL_QUANTITY NUMBER,
    TOTALSALE NUMBER,
    CONSTRAINT PKSALESTOCK PRIMARY KEY (SALEID, DVDNAME),
    CONSTRAINT FKSALE FOREIGN KEY (SALEID) REFERENCES SALE (SALEID) ON DELETE CASCADE,
    CONSTRAINT FKSTOCK FOREIGN KEY (DVDNAME) REFERENCES STOCK (DVDNAME) ON DELETE CASCADE);
     
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Ace1,

    You can use join operation to get multiplication of those two records from different tables as shown in below example:

    Code (Text):


    SQL> Select a.saleid Sale_id, a.SALEUNITPRICE Unit_Price, b.SAL_QUANTITY Sal_Quantity, ( a.SALEUNITPRICE * b.SAL_QUANTITY ) Total_Sales
    from SALE a , SALESTOCK b
    where a.saleid = b.saleid ;

    result: -
    *****
    Sale_id  Unit_Price  Sal_Quantity  Total_sales
    *****  *******  **********  ********
    1          200          20                4000
    2          250          10                2500
    3          200          10                2000
    4          250          20                5000
    5          200          20                4000
    6          250          10                2500


     
    You can write as shown above to get what you required. This was explained to just give an idea on how to do it. Please change it according to your requirement. This statement was tested with few records as well.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is a homework problem so please do not expect us to solve it for you. Show us what you have written so far and we will be happy to correct mistakes in coding and logic and get you on the right track.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    A different set of tables should have been used for this example. What you have provided has basically solved his homework for him. All he needs to do is copy and paste your example into his assignment.
     
  7. miteshaegis

    miteshaegis Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    Yes You can get Unit Total of Price * Quantity.

    thanks!