This discussion is archived
12 Replies Latest reply: Sep 13, 2013 8:56 AM by Greg.Spall RSS

Error!  Totals included in Sort.  How do I exclude the Totals?

V Rickert Newbie
Currently Being Moderated

Hello.

I have a summary report that is including the Totals as part of the sort.  How do I get exclude the total from the sort?

Here is my code:

 

<code>

select nvl(to_char(vendor),'Totals:')

vendor,

po_number,

contract_number,

po_amount,

inv_total,

cast(round(case when po_amount = 0 then 0 else nvl(inv_total/po_amount,0) end, 2)*100 as varchar(10)) || '%' percent

from

(

select

vendor,

po_number,

contract_number,

sum(nvl(po_amount,0)) po_amount,

sum(nvl(inv_total,0)) inv_total

from

(

select b.vendor,

b.po_number,

b.wbs,

b.contract_number,

b.po_amount,

a.inv_total

from (

select

po_number,

wbs,

sum(inv_total)inv_total

from

invoice,invoice_details,project

where invoice.inv_id = invoice_details.inv_id

and substr(invoice_details.wbs,1,6) = project.wbs_sequence

and project.project_number = nvl(:F101_FPC_NUMBER,project.project_number)

group by po_number, wbs) a,

(

select vendor, po_number, wbs, contract_number, sum(po_amount)po_amount

from po, po_details, project

where po.po_id = po_details.po_id

and substr(po_details.wbs,1,6) = project.wbs_sequence

and project.project_number = nvl(:F101_FPC_NUMBER,project.project_number)

group by vendor, po_number, contract_number, wbs)b

where a.po_number(+)=b.po_number

and a.wbs(+) = b.wbs)

group by grouping sets((),(

vendor,

po_number,

wbs,

contract_number)))

order by (round(case when po_amount = 0 then 0 else nvl(inv_total/po_amount,0) end, 2))*100 desc

</code>

 

As you can see, the SQL is fairly complicated.  I need to find a way to keep the 'TOTAL' out of the sort.

Any ideas?

Thank you in advance!

  • 1. Re: Error!  Totals included in Sort.  How do I exclude the Totals?
    Greg.Spall Expert
    Currently Being Moderated

    Ok, obviously we can't run that query, so staring at a big wall of text isn't very efficient

     

    Can you please post  small, self-contained, working sample.  Complete with create tables, and inserts.  Then it should be easier for somebody here to help you out.

     

    THanks!

     

    [edit]

    Read this ... https://forums.oracle.com/message/9362002#9362002

    [/edit]

  • 2. Re: Error!  Totals included in Sort.  How do I exclude the Totals?
    V Rickert Newbie
    Currently Being Moderated

    I posted this originally in Application Express but since it was SQL related and someone already said to another to post their SQL question in the SQL forum, I did the same.  However, I have no authority to post a pic of my report here so I'm basically stuck.

    Can someone allow me access to paste a pic here?  It's only 150KB.  Otherwise, please unlock my post on Application Express forum and maybe I can post it there.

    Thank you!

  • 3. Re: Error!  Totals included in Sort.  How do I exclude the Totals?
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    As Greg said, whenever you have a question, you should post a complete script that the people who want to help you can run to re-create the problem and test their ideas.  That often includes CREATE TABLE and INSERT statements for some sample data, though sometimes it's possible to show the problem using commionly available tables, such as those in the scott schema.

    One way or another, there has to be a clear set of sample data, and you have to show exactly what results you want from that sample data.

    As Greg also said, see the forum FAQ: https://forums.oracle.com/message/9362002   Whatever time it takes to read and follow it will be an excellent investment.

     

    I think what you're trying to do is very much like this: Show the salaries of employees from scott.emp, with the total salaries for each job, and the grand total for the whole result set, like this:

    ENAME      JOB       TOTAL_SAL

    ---------- --------- ---------

    KING       PRESIDENT      5000

    FORD       ANALYST        3000

    SCOTT      ANALYST        3000

    JONES      MANAGER        2975

    BLAKE      MANAGER        2850

    CLARK      MANAGER        2450

    ALLEN      SALESMAN       1600

    TURNER     SALESMAN       1500

    MILLER     CLERK          1300

    MARTIN     SALESMAN       1250

    WARD       SALESMAN       1250

    ADAMS      CLERK          1100

    JAMES      CLERK           950

    SMITH      CLERK           800

               MANAGER        8275

               ANALYST        6000

               SALESMAN       5600

               PRESIDENT      5000

               CLERK          4150

                             29025

    The results are sorted such that the individual employees are all displayed first (in descending order by total_sal), then the totals for all the job totals (also in descending order by total_sal), and finally the grand total is shown.

    One way to get those results is to include GROUPING in the ORDER BY clause, like this:

     

    SELECT    ename

    ,         job

    ,         SUM (sal)   AS total_sal

    FROM      scott.emp

    GROUP BY  GROUPING SETS ( (job, ename)

                            , (job)

                            , ()

                            )

    ORDER BY  GROUPING (ename)

    ,         GROUPING (job)

    ,         total_sal      DESC

    ;

    This makes no assumptions about ename or job being NULL; if you happened to have a row where ename was NULL, that employee would still be sorted along with the other employees in order by total_sal.

     

    However, there does seem to be a possible significant difference between your problem and this example:  Maybe you're doing a much more complicated query, that requires aggregating in a sub-query.  As always, the ORDER BY has to take place in the main query, but you can only call GROUPING in the sub-query where the GROUP BY is done, so how can we get the correct ordering in the main query?  By saving the results of GROUPING in new coloumns (grouping_ename and grouping_job in the example below), and using those columns in the main query's ORDER BY clause:

     

    WITH   aggregate_data  AS

    (

        SELECT    ename

        ,         job

        ,         SUM (sal)          AS total_sal

        ,         GROUPING (ename)   AS grouping_ename

        ,         GROUPING (job)     AS grouping_job

        FROM      scott.emp

        GROUP BY  GROUPING SETS ( (job, ename)

                                , (job)

                                , ()

                                )

    )

    SELECT    ename, job, total_sal

    FROM      aggregate_data

    ORDER BY  grouping_ename

    ,         grouping_job

    ,         total_sal DESC

    ;

    Notice the sub-query aggregate_data of the 2nd example is almost identical to the 1st example; the only difference is that, instead of having an ORDER BY clause, it has extra columns in the SELECT clause, so that the ORDER BY can be done later.

  • 4. Re: Error!  Totals included in Sort.  How do I exclude the Totals?
    Greg.Spall Expert
    Currently Being Moderated

    Not a pic ... read the link ... create a working sample, and post it.

  • 5. Re: Error!  Totals included in Sort.  How do I exclude the Totals?
    kendenny Expert
    Currently Being Moderated

    Your query starts out with select nvl(to_char(vendor),'Totals:')

    I take it you want the one where vendor is null to be sorted last regardless of the value of the order by column, right. Then change your order by to:

    order by case when vendor is null then 1 else 0 end,(round(case when po_amount = 0 then 0 else nvl(inv_total/po_amount,0) end, 2))*100

  • 6. Re: Error!  Totals included in Sort.  How do I exclude the Totals?
    V Rickert Newbie
    Currently Being Moderated

    Kendenny:  I tried your suggestion but the Totals entry still sorts in the middle of the group for some reason.  You are right.  I AM trying to force the 'Totals:' to appear as the last line.

     

    You can go to the APEX.ORACLE.COM website and look at my application:

    WORKSPACE: PSGCA

    USERNAME: Valerie.Belcher@Williams.com

    PASSWORD:  Rickert50

    Run Application I and I

    Log in as:  guru

    Password: guru

    Select Reports in the Menu and click on PO Invoice Summary

    I have a sample of only two entries with a Totals included.  You can see that the Totals sorts in the middle of the report.  I need to force it to be at the end of the report.

    Thanks in advance for any suggestions you might have.  

  • 7. Re: Error!  Totals included in Sort.  How do I exclude the Totals?
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    Both the solution I posted and the one Kendenny posted work for me.  What are you doing differently?
    Please post a complete test script that people can run to re-create the problem and test their ideas.

  • 8. Re: Error!  Totals included in Sort.  How do I exclude the Totals?
    V Rickert Newbie
    Currently Being Moderated

    Hello!

    The code I am using is just like above but with the  order by clause updated.  You can log into my account on APEX.ORACLE.COM, select PO Invoice Summary Report, see the result and just edit the page to really see the guts behind the report.  I'll post the code with my updates.  The SQL statement is rather difficult to follow but here it is:

     

    <code>

    select nvl(to_char(vendor),'Totals:')

    vendor,

    po_number,

    contract_number,

    po_amount,

    inv_total,

    cast(round(case when po_amount = 0 then 0 else nvl(inv_total/po_amount,0) end, 2)*100 as varchar(10)) || '%' percent

    from

    (

    select

    vendor,

    po_number,

    contract_number,

    sum(nvl(po_amount,0)) po_amount,

    sum(nvl(inv_total,0)) inv_total

    from

    (

    select b.vendor,

    b.po_number,

    b.wbs,

    b.contract_number,

    b.po_amount,

    a.inv_total

    from (

    select

    po_number,

    wbs,

    sum(inv_total)inv_total

    from

    invoice,invoice_details,project

    where invoice.inv_id = invoice_details.inv_id

    and substr(invoice_details.wbs,1,6) = project.wbs_sequence

    and project.project_number = nvl(:F101_FPC_NUMBER,project.project_number)

    group by po_number, wbs) a,

    (

    select vendor, po_number, wbs, contract_number, sum(po_amount)po_amount

    from po, po_details, project

    where po.po_id = po_details.po_id

    and substr(po_details.wbs,1,6) = project.wbs_sequence

    and project.project_number = nvl(:F101_FPC_NUMBER,project.project_number)

    group by vendor, po_number, contract_number, wbs)b

    where a.po_number(+)=b.po_number

    and a.wbs(+) = b.wbs)

    group by grouping sets((),(

    vendor,

    po_number,

    wbs,

    contract_number)))

    order by case when vendor is null then 1 else 0 end,(round(case when po_amount = 0 then 0 else nvl(inv_total/po_amount,0) end, 2))*100 desc

    </code>

     

    Hope this helps! 

  • 9. Re: Error!  Totals included in Sort.  How do I exclude the Totals?
    Greg.Spall Expert
    Currently Being Moderated

    Just a crazy hunch ... change your first line from this:

     

    select nvl(to_char(vendor),'Totals:')   vendor,

     

    To this:

    select nvl(to_char(vendor),'Totals:')  vendor_t,

     

    See what it does

  • 10. Re: Error!  Totals included in Sort.  How do I exclude the Totals?
    V Rickert Newbie
    Currently Being Moderated

    OK!  I'll try that! 

  • 11. Re: Error!  Totals included in Sort.  How do I exclude the Totals?
    V Rickert Newbie
    Currently Being Moderated

    OMG!!!  That did it!!!  Thanks so much!  YAY!  My report is now working!!! 

  • 12. Re: Error!  Totals included in Sort.  How do I exclude the Totals?
    Greg.Spall Expert
    Currently Being Moderated

    Neat. I wasn't 100% sure if Oracle actually used your alias in that order by or not, but something told me it did (and I was too busy to actually test it )

    Meh, you're problem, you test it. lol

     

    Glad to have helped!!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points