OraFAQ Forum: SQL & PL/SQL » sum up columns

Home » SQL & PL/SQL » SQL & PL/SQL » sum up columns

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sum up columns [message #219619]

Thu, 15 February 2007 03:28

gtriant
Messages: 42
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???

Report message to a moderator

Re: sum up columns [message #219622 is a reply to message #219619]

Thu, 15 February 2007 03:38

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);

Report message to a moderator

Re: sum up columns [message #219625 is a reply to message #219619]

Thu, 15 February 2007 03:49

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?

Report message to a moderator

Re: sum up columns [message #219640 is a reply to message #219619]

Thu, 15 February 2007 04:36

gtriant
Messages: 42
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)???

Report message to a moderator

Re: sum up columns [message #219656 is a reply to message #219640]

Thu, 15 February 2007 05:36

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

Report message to a moderator

Previous Topic:	How to delete rows in column ?
Next Topic:	3 questions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 13:37:49 CST 2024