oracle 10g query

110 pts.
Tags:
Oracle 10g
Oracle Query
i need sum of income amount from income table and sum of expense amount from expense table to calculate the profit or loss. i have no idea how can i link these two tables.
ASKED: August 7, 2009  8:31 AM
UPDATED: November 10, 2013  3:03 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Zaman31502. You asked a similar question a few days ago, and we asked for more details, but you did not provide any.

We have no way to know how you can link those two tables without knowing the structure of those tables.

If those tables are really unrelated as you said in your previous question, then maybe you don’t have to get the information from both in one query, but execute one query for each table, and make the calculations in your program or in a stored procedure.

We could give you some suggestions, but everything would be based on assumptions, because we do not have any details.

Please provide more details, and I’m sure you will get the answer you are expecting.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Jsl25

    carlosd is correct that providing a detail reply to this inquiry would be made much easier by having a more detailed definition of the source tables. however, with this being understood, there is yet a generic approach which will work in all cases where two values either at an individual record level or as the sum value for a column in two (or more) distinct tables are necessary to calculate a composite value. this approach is presented here.

    You indicate that one table contains Income values while another potentially non-relational table contains Expense values.

    In both cases, one must assume that there exists filter values for a WHERE clause for the query against each table which will delivered a commonly bounded dataset for comparison. For the example presented below I assume that the bounding values are date related.

    Generic query

    /* assumptions
    Income values from IncomeTbl
    Expense value from ExpenseTbl
    */

    SELECT INCOME, EXPENSE, INCOME - EXPENSE AS PROFIT
    FROM (
    SELECT SUM(SUMINCOME) AS INCOME, SUM(SUMEXPENSE) AS EXPENSE
    FROM (
    SELECT SUM(IncomeColValue) AS SUMINCOME, 0 AS SUMEXPENSE
    FROM IncomeTbl
    -- WHERE clause is required to determine collection of IncomeTbl records to SUM
    WHERE TO_DATE(DateCol, 'MM/DD/YYYY') >= TO_DATE(&param)StartDate, 'MM/DD/YYYY')
    AND TO_DATE(DateCol, 'MM/DD/YYYY') <= TO_DATE(&param)EndDate, 'MM/DD/YYYY')
    UNION
    SELECT 0 AS SUMINCOME, SUM(ExpenseColValue) AS SUMEXPENSE
    FROM ExpenseTbl
    -- WHERE clause is required to determine collection of ExpenseTbl records to SUM
    WHERE TO_DATE(DateCol, 'MM/DD/YYYY') >= TO_DATE(&param)StartDate, 'MM/DD/YYYY')
    AND TO_DATE(DateCol, 'MM/DD/YYYY') <= TO_DATE(&param)EndDate, 'MM/DD/YYYY')
    )
    )

    Now, IF you are trying to determine the PROFIT for a related SET of IncomeTbl and ExpenseTbl records, additional definition of the structure of these tables is required to formulate a query.

    5 pointsBadges:
    report
  • FredMate

    If you're just trying to record your income and expenses then work out how much dosh you have, do this:

    CREATE table "INCOME" (
        "DOSH"       NUMBER(5,2)
    )
    /

    insert into INCOME values (50);

    CREATE table "EXPENSE" (
        "DOSH"       NUMBER(5,2)
    )
    /

    insert into EXPENSE values (5);
    insert into EXPENSE values (15);

    SELECT SUM(DOSH) -
       (SELECT SUM(DOSH) FROM EXPENSE)
    FROM INCOME
    GROUP BY DOSH;

    Normally DB designers would be thinking of DB for more than one individual and therefore another table PERSON would be created and that would like to both INCOME and EXPENSE, thus providing the so expected join; and you would restrict for one individual person.

     

     

    105 pointsBadges:
    report
  • FredMate

    Opps - correction, try:

    declare
       IND NUMBER(5,2) := 0;  
       OUTD NUMBER(5,2) := 0;  
       TOTALD NUMBER(5,2) := 0;
    begin
       SELECT SUM(dosh)INTO ind FROM INCOME;
       SELECT SUM(dosh)INTO outd FROM EXPENSE;

       dbms_output.put_line ('Total dosh: ' || TOTALD);

    end;
    /

    105 pointsBadges:
    report
  • FredMate

    Finally.

    declare
       IND NUMBER(5,2) := 0;  
       OUTD NUMBER(5,2) := 0;  

    begin
       SELECT SUM(dosh)INTO ind FROM INCOME;
       SELECT SUM(dosh)INTO outd FROM EXPENSE;
      
       dbms_output.put_line ('Total dosh: ' || (IND - OUTD));

    end;
    /

    How do you delete previous error posts?

    105 pointsBadges:
    report
  • FredMate

    Yes the Union answer is easier.

    SELECT SUM(SUMDOSH)
    FROM (
    SELECT SUM(dosh) AS SUMDOSH
     FROM INCOME
    UNION
    SELECT (SUM(dosh) * -1) AS SUMDOSH
     FROM Expense
    )

    105 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following