Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2008
    Posts
    4

    Unanswered: How to subtract quantities in sql? Formula columns?

    I have created an oracle report

    1)One form records the Supplies INCOMING

    2) One form records Supplies being USED till this date

    I Need to subtract the two. I have created two queries in oracle reports the first query Sums up all the supplies. 2) Sums up all supplies USEd

    1)

    Select distinct a.ItemName ||' '|| b.ItemSize,sum(b.Quantity)
    from Thi_Admin.CathLabItems a,Thi_Admin.CathLabSuppliesDetails b
    where a.itemcode=b.itemcode
    group by a.ItemName ||' '|| b.ItemSize;

    2) SELECT distinct c.ItemName ||' '|| a.ItemSize, sum(a.Quantity)

    FROM

    Thi_Admin.PatientsCathItemsUsed a, Thi_Admin.PatientsCathProcedures b,Thi_Admin.CathLabItems c

    WHERE a.CathID = b.CathID

    And a.ItemCode = c.ItemCode

    And To_Date(To_Char(b.DoProcedure,'DD/MM/YYYY'),'DD/MM/YYYY') <= ate1

    group by c.ItemName ||' '|| a.ItemSize;



    Thanks!!

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Outer Join

    OUTER JOIN both queries and add a column with the difference between the SUM()s.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •