1. Senior Member
Join Date
Jan 2001
Posts
318
select A.Work_item_code,A.work_description,sum(mat_amount) from mwebwork A, MWEBWORK B, mwebmatrix where B.WORK_ENTITY_TYPE=4 and mat_work_id=B.work_id and mat_category between 2500 and 3999 and
mat_date between to_date('01-Dec-2000', 'DD-MON-YYYY') AND TO_DATE('10-DEC-2000', 'DD-MON-YYYY') AND A.WORK_ID=B.WORK_PAR6 AND A.WORK_LEVEL=6 GROUP BY A.WORK_ID, A.WORK_ITEM_CODE, A.WORK_DESCRIPTION

Is there any way I can get the Summation of Sum(mat_amount) at the bottom of this select statement ?

Why it takes about 10 minutes ??

thanks

2. lnr
Junior Member
Join Date
Jan 2001
Posts
60

## waht is the version u are using

Hi ,

How do u want exactly ,is it like this

dept no sum(salary)
10 100
20 300

-----------------------
400

If this is the o/p u want and u are using 8i i can explain u

Thanks
lnreddy

3. Member
Join Date
Feb 2000
Posts
175
Hi,

If you want a total of the sum(mat_amount) field you can compute the total using the following:

column total format 9999990

compute sum of total on report

select A.Work_item_code,A.work_description,sum(mat_amount) "total"
from mwebwork A, MWEBWORK B, mwebmatrix
where B.WORK_ENTITY_TYPE=4 and mat_work_id=B.work_id and mat_category between 2500 and 3999 and
mat_date between to_date('01-Dec-2000', 'DD-MON-YYYY') AND TO_DATE('10-DEC-2000', 'DD-MON-YYYY') AND A.WORK_ID=B.WORK_PAR6 AND A.WORK_LEVEL=6 GROUP BY A.WORK_ID, A.WORK_ITEM_CODE, A.WORK_DESCRIPTION;

clear computes

As for the time issue...Have you got the columns in the where clause indexed properly?
Hope this helps.

Let me know if it's what you're looking for...

Kind Regards

Moff

4. Senior Member
Join Date
Jan 2001
Posts
318
lnreddy is right I want total of sum(mat_amount) at the end.
sum(mat_amount)
100
200
300
----------
600 total sum(sum(mat_amount))... how do I get this ????
I am using Oracle 8.
Yes, indexes could be a problem I am looking at it.

5. Senior Member
Join Date
Aug 2000
Location
Belgium
Posts
342
take a look at the ROLLUP and CUBE keywords in the "Oracle8i Datawarehousing Guide " section 17 SQL for Analysis.

I think the syntax is something like this :

select blabla1,blabla2, sum(amount)
from blablatable
group by rollup(blabla1,blabla2)

Regards
Gert

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•