How to display sum of amount at bottom of row

In this forum you can share stuff related to Oracle 11g, 10g, 9i.
Post Reply
yknev
Posts: 49
Joined: Wed Feb 17, 2010 3:08 am
Location: India

How to display sum of amount at bottom of row

Post by yknev »

Dear all,

How to print the Total amount/salary at the bottom of line without using break on and compute commands.
amirtai
Posts: 138
Joined: Sat Apr 08, 2006 5:54 pm
Location: Canada
Contact:

Post by amirtai »

Hi there

This is the simplest example but it'll give you some idea:

SELECT ename, sal
FROM emp
UNION
SELECT '__Total -> ' , sum(sal)
FROM emp;

Have fun.
yknev
Posts: 49
Joined: Wed Feb 17, 2010 3:08 am
Location: India

Post by yknev »

Dear Amirtai,

Thanks for given idea. But, in my query date column is present at this scenario how to write a query. Kindly suggest to me. For more clarification i mentioned one simple query in below.

select ename,sal,job,hiredate from emp
union
select 'Total',sum(sal),'', <font color="red">DATE Column </font id="red"> from emp

In above example, how can a write same date expressioin(above red color).
admin
Posts: 2062
Joined: Fri Mar 31, 2006 12:59 am
Location: Pakistan
Contact:

Post by admin »

What and where you want to achieve exactly like reports builder or what. tks
amirtai
Posts: 138
Joined: Sat Apr 08, 2006 5:54 pm
Location: Canada
Contact:

Post by amirtai »

Hi there

I believe that should work fine for your scenario:

SELECT ename, sal, job, hiredate
FROM emp
UNION
SELECT '__Total -> ', sum(sal), ' ',hiredate
FROM emp
group by hiredate


Amir
yknev
Posts: 49
Joined: Wed Feb 17, 2010 3:08 am
Location: India

Post by yknev »

Dear Amirtai,

I will agree with u. but in my scenario more than 15 columns are present including date columns. So that i want to display sum of amount at the last row without using GROUP BY clause.
amirtai
Posts: 138
Joined: Sat Apr 08, 2006 5:54 pm
Location: Canada
Contact:

Post by amirtai »

Hello

You could use NULL for each column in the second query, for example:

select first_name,last_name,hiredate,sal
from l_employees
union
select 'total ->',null,null,sum(sal)
from l_employees

Amir
yknev
Posts: 49
Joined: Wed Feb 17, 2010 3:08 am
Location: India

Post by yknev »

Dear Amirtai,

Thanks for prompt response. But, In my scenario <b>query takes a long time for execution</b>. Please let me know, how can reduce the execution time. And also i want to present a number data type instead of varchar type at the bottom of result row. I mentioned one example below

select ename,sal,hiredate from emp
union
select <font color="red">sum(case when sal<0 then 1 else 0 end)</font id="red">,sum(sal),sysdate from emp
In above example, the red color mentioned result is number data type but ENAME column is varchar type. Is possible to print the number data type in varchar type.
amirtai
Posts: 138
Joined: Sat Apr 08, 2006 5:54 pm
Location: Canada
Contact:

Post by amirtai »

Hello

Please post the execution plan for your query and we'll see what could be done to optimize it.

The pre-condition for using UNION clause is, column type must match for each corresponding column in the second query or it can be NULL. You could convert the column type in your second query, for instance:

select ename,sal,hiredate
from emp
union
select to_char(count(case when sal > 0 then 1 else null end)),sum(sal),sysdate
from emp

Amir
yknev
Posts: 49
Joined: Wed Feb 17, 2010 3:08 am
Location: India

Post by yknev »

Dear Amirtai,

I print the sum of salary at the last row. But my query takes a long time. Can u give me any suggestion regarding, how to reduce the execution time.(In my scenario more than 3000 rows selected)
Post Reply

Who is online

Users browsing this forum: No registered users and 0 guests