Urgent query help please

[sonaliak]

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

[lnr]

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

[moff]

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

[sonaliak]

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.

[denevge]

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