Urgent query help please
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Urgent query help please

  1. #1
    Join Date
    Jan 2001
    Posts
    318

    Angry

    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
    Sonali

  2. #2
    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
    html code is off

  3. #3
    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. #4
    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.
    Sonali

  5. #5
    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
  •  



Click Here to Expand Forum to Full Width