Home » SQL & PL/SQL » SQL & PL/SQL » Calculated Field Need SUM - Add- Subract
Calculated Field Need SUM - Add- Subract [message #233558] Thu, 26 April 2007 10:51 Go to next message
hdogg
Messages: 93
Registered: March 2007
Member
Here's My Query:

SELECT PERIOD, sum(A) AS A , sum(B) AS B FROM DATA GROUP BY PERIOD ORDER BY to_date(PERIOD,'mm/dd/rr')


I would like to create a third column, RESULT

sum(A) - sum(B) = RESULT

How do I do that?
Re: Calculated Field Need SUM - Add- Subract [message #233559 is a reply to message #233558] Thu, 26 April 2007 10:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What happens if you add that column?
[Edit: or do you mean you want sum(sum(a)) - sum(sum(b))? Very confusing to alias your summation columns with the original column-names]

[Updated on: Thu, 26 April 2007 10:58]

Report message to a moderator

Re: Calculated Field Need SUM - Add- Subract [message #233560 is a reply to message #233559] Thu, 26 April 2007 11:01 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
basically I am creating a view....

the sum(A) is REVENUE, the sum(B) is COSTS, however there is no EARNINGS column, so i would like EARNINGS to be the third\

-Hyrum
Re: Calculated Field Need SUM - Add- Subract [message #233566 is a reply to message #233560] Thu, 26 April 2007 11:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry, I still don't know what should be in the third (earnings) column.
COST  REVENUE      PERIOD
----  -------  ----------
   1        2  01-01-2007
   2        3  01-01-2007
   1        4  02-01-2007
   2        4  02-01-2007


If you want to display your earnings per day, you can add your column like you described:
SQL> create table faq
  2  ( period  date
  3  , costs   number
  4  , revenue number
  5  );

Table created.

SQL> insert all
  2  into faq values(to_date('01-01-2007', 'dd-mm-yyyy'), 1, 2)
  3  into faq values(to_date('01-01-2007', 'dd-mm-yyyy'), 2, 3)
  4  into faq values(to_date('02-01-2007', 'dd-mm-yyyy'), 1, 4)
  5  into faq values(to_date('02-01-2007', 'dd-mm-yyyy'), 2, 4)
  6  select * from dual
  7  /

4 rows created.

SQL> select period
  2  ,      sum(costs)                total_cost_per_day
  3  ,      sum(revenue)              total_revenue_per_day
  4  ,      sum(revenue) - sum(costs) total_earnings_per_day
  5  from   faq
  6  group  by period;

PERIOD    TOTAL_COST_PER_DAY TOTAL_REVENUE_PER_DAY TOTAL_EARNINGS_PER_DAY
--------- ------------------ --------------------- ----------------------
02-JAN-07                  3                     8                      5
01-JAN-07                  3                     5                      2


[Edit: added query]

[Updated on: Thu, 26 April 2007 11:22]

Report message to a moderator

Re: Calculated Field Need SUM - Add- Subract [message #233569 is a reply to message #233558] Thu, 26 April 2007 11:24 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
REVENUE COST (REVENUE-COST) PERIOD
------- ------- -------------- ----------
2 1 1 01-01-2007
3 2 1 01-01-2007
4 1 3 02-01-2007
4 2 2 02-01-2007

(REVENUE-COST) aka EARNINGS

I don't want to store the data, i just want it calculated when i run the query.

-Hyrum

[Updated on: Thu, 26 April 2007 11:24]

Report message to a moderator

Re: Calculated Field Need SUM - Add- Subract [message #233572 is a reply to message #233569] Thu, 26 April 2007 11:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I did not store anything.
It is still not clear to me whether you want your total earnings or your earnings per day.
If you want that:
SQL> select period
  2  ,      total_cost_per_day
  3  ,      total_revenue_per_day
  4  ,      total_earnings_per_day
  5  ,      sum(total_earnings_per_day) over () as grand_total_earnings
  6  from  (select period
  7         ,      sum(costs)                total_cost_per_day
  8         ,      sum(revenue)              total_revenue_per_day
  9         ,      sum(revenue) - sum(costs) total_earnings_per_day
 10         from   faq
 11         group  by period
 12        );

PERIOD    TOTAL_COST_PER_DAY TOTAL_REVENUE_PER_DAY TOTAL_EARNINGS_PER_DAY GRAND_TOTAL_EARNINGS
--------- ------------------ --------------------- ---------------------- --------------------
01-JAN-07                  3                     5                      2                    7
02-JAN-07                  3                     8                      5                    7

[Updated on: Thu, 26 April 2007 12:02]

Report message to a moderator

Re: Calculated Field Need SUM - Add- Subract [message #233575 is a reply to message #233572] Thu, 26 April 2007 12:27 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
Oh, gotcha, earnings per day.
Re: Calculated Field Need SUM - Add- Subract [message #233596 is a reply to message #233572] Thu, 26 April 2007 15:19 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
Is that impossible?
Re: Calculated Field Need SUM - Add- Subract [message #233598 is a reply to message #233558] Thu, 26 April 2007 15:32 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
SELECT PERIOD, sum(A) A , sum(B) B, sum(a) - sum(b) c
FROM DATA
GROUP BY PERIOD
ORDER BY to_date(PERIOD,'mm/dd/rr');
Re: Calculated Field Need SUM - Add- Subract [message #233604 is a reply to message #233598] Thu, 26 April 2007 17:33 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
Thanks! That works great... One more thing...

Now i need to subtotal an entire column of results, without grouping it... simply the sum...

how do i do that?
Re: Calculated Field Need SUM - Add- Subract [message #233606 is a reply to message #233558] Thu, 26 April 2007 18:25 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>how do i do that?
By continuing to have other folks do your work for you, if you are fortunate.
Or you could get resourceful & learn how to solve your own problems.
Re: Calculated Field Need SUM - Add- Subract [message #233609 is a reply to message #233606] Thu, 26 April 2007 20:54 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
hey.. thanks for all the help... i researched and found out the answer...
Re: Calculated Field Need SUM - Add- Subract [message #233611 is a reply to message #233558] Thu, 26 April 2007 22:35 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
> i researched and found out the answer...
CONGRATULATIONS!
Please post the solution so others can benefit, too.
Re: Calculated Field Need SUM - Add- Subract [message #233621 is a reply to message #233604] Thu, 26 April 2007 23:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
hdogg wrote
Is that impossible?

hdogg wrote
Thanks! That works great... One more thing...

Now i need to subtotal an entire column of results, without grouping it... simply the sum...

how do i do that?

Did you even READ my posts?
I gave you the answer to BOTH these questions before you asked!
Re: Calculated Field Need SUM - Add- Subract [message #233824 is a reply to message #233611] Fri, 27 April 2007 12:36 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
A couple maxims when summing fields:

1) When doing the select statement only select the fields that will be summed and/or the the field that will group it.

2) If trying to sum a single column only select that field. Do not use a GROUP BY condition.

3) To create a calculated field:
Table A, Table B
Simple take sum(A) - sum(B) = C.

I hope this helps-

-Hyrum

Any questions feel free to send me a message, and I would be glad to help.
Re: Calculated Field Need SUM - Add- Subract [message #233834 is a reply to message #233824] Fri, 27 April 2007 13:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Are these corrections to the code we provided?
Re: Calculated Field Need SUM - Add- Subract [message #233837 is a reply to message #233834] Fri, 27 April 2007 13:34 Go to previous message
hdogg
Messages: 93
Registered: March 2007
Member
No, I am just summing up the discussion, and what I had researched for others to benefit.
Previous Topic: Table_name as variable
Next Topic: Renaming Check Constraints in 8.1.7
Goto Forum:
  


Current Time: Thu Dec 08 14:32:03 CST 2016

Total time taken to generate the page: 0.11690 seconds