Home » SQL & PL/SQL » SQL & PL/SQL » sum up columns
sum up columns [message #219619] Thu, 15 February 2007 03:28 Go to next message
gtriant
Messages: 41
Registered: September 2006
Member
HI!
Is there a way that i can have a last row in my generated table, that sums up all the above rows???

For instance:
I have a table like the following

Employee | Salary
Jim | 2000
John | 1500
Maria | 2000
Total | 5500


How can i generate the total from my query???
Re: sum up columns [message #219622 is a reply to message #219619] Thu, 15 February 2007 03:38 Go to previous messageGo to next message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
Use one of the aggregation extensions. Very useful

select nvl(employee, 'total'), sum(salary)
from tb_name
group by rollup(employee);
Re: sum up columns [message #219625 is a reply to message #219619] Thu, 15 February 2007 03:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No.

As @pmaupoil shows, you can add a row to the table with the sum of all the data, but:
1) It's not a 'Last' row, as there is no such concept in a relational db, unless you've got an Order By clause
2) Maintaining this value is a non-trivial excercise, and is very non-trivial in a multi user environment.

A better solution would be to create a view that returns all the rows from the table, and has a Union clause to add a summary row - this will have the advantage of always returning the correct current value

Why exactly do you want ot do this?
Re: sum up columns [message #219640 is a reply to message #219619] Thu, 15 February 2007 04:36 Go to previous messageGo to next message
gtriant
Messages: 41
Registered: September 2006
Member
Thank you very much for your answer, but my query is already quite complicated (i already use sums and joins), and i can't adjust what you proposed to my query.

The query that i already have is this:

select
b.dscr supplier,
sum(c.participates) participates,
sum(c.is_active) is_active,
sum(c.trans_participates) trans_participates,
sum(c.trans_is_active) trans_is_active,
count(*) total,
from chamber c, backend_app_supplier b
where c.backend_app_supplier_id=b.id
group by b.dscr

I must have one last row with the totals of those sums and of that count.
is that possible?
if not, can i generate that last row with another query and "stick it" to the main table (maybe with a union)???
Re: sum up columns [message #219656 is a reply to message #219640] Thu, 15 February 2007 05:36 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, if you absolutely must, then I feel no compunction at all in breaking out some of the more obscure SQL you'll see this week

drop table rollup_test;

create table rollup_test (col_1 varchar2(10), col_2  number, col_3 number);

insert into rollup_test values ('A',5,3);
insert into rollup_test values ('A',6,3);
insert into rollup_test values ('A',6,2);
insert into rollup_test values ('A',0,4);
insert into rollup_test values ('B',5,3);
insert into rollup_test values ('B',5,3);
insert into rollup_test values ('B',5,3);
insert into rollup_test values ('B',5,3);
insert into rollup_test values ('C',5,3);
insert into rollup_test values ('C',5,3);

From this setup, you can use the ROLLUP (search for that here) to produce a total row.
SQL> select col_1
  2        ,sum(col_2)
  3        ,sum(col_3)
  4        ,count(*)
  5  from   rollup_test
  6  group by rollup(col_1);

COL_1      SUM(COL_2) SUM(COL_3)   COUNT(*)
---------- ---------- ---------- ----------
A                  17         12          4
B                  20         12          4
C                  10          6          2
                   47         30         10

You can also use the GROUPING function to let you identify (or sort by) the summary row that this adds.
SQL> select decode(grouping(col_1),0,col_1,'Total') col_1
  2        ,sum(col_2)
  3        ,sum(col_3)
  4        ,count(*)
  5  from   rollup_test
  6  group by rollup (col_1);

COL_1      SUM(COL_2) SUM(COL_3)   COUNT(*)
---------- ---------- ---------- ----------
A                  17         12          4
B                  20         12          4
C                  10          6          2
Total              47         30         10
Previous Topic: How to delete rows in column ?
Next Topic: 3 questions
Goto Forum:
  


Current Time: Fri Dec 09 19:16:36 CST 2016

Total time taken to generate the page: 0.13212 seconds