12 Replies Latest reply: Sep 13, 2013 10:56 AM by Greg Spall RSS

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

    V Rickert

      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

          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

            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

              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

                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

                  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

                    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

                      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

                        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

                          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

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

                            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

                              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!!