![rowwise grandtotal in oracle11g rowwise grandtotal in oracle11g](https://i.stack.imgur.com/aMA9s.png)
The WITH…BY can be incorporated into existing SQL to provide a subtotal. To obtain a subtotal, the WITH incorporates a BY designation.
![rowwise grandtotal in oracle11g rowwise grandtotal in oracle11g](http://3.bp.blogspot.com/-u2YvSphy__w/T6DIrk88vPI/AAAAAAAAAkQ/HdiEyBYg1UA/w1200-h630-p-k-no-nu/R1.png)
Notice too that the totals can be formattedindividually and can be different.Īs well as the ability to provide a total, Teradata can also provide one or more subtotals. (TITLE 'Total Salary:', FORMAT '$$$$,$$9.99')Īs you can see in Teradata, it is relatively easy to obtain a final number for display from an aggregate, title it and format it, all this along with the detail row data that created it. The above SELECT could be changed to the following:įROM Employee_tableWITH SUM(Salary) (TITLE 'Total Salary:')įurthermore, it might be desirable to include an average salary to the previous example as seen in the next SELECT can accomplish this:įROM Employee_tableWITH AVG(salary) (TITLE 'Average Salary:'), SUM(salary ) To do this, simply use the TITLE function and Teradata labels the newly created line using the text that is specified. Since it is preferable not to see a label called SUM(salary), the subtotal needs to have a name or title associated with it. The next SELECT shows the detail of the rows in the table with final total displayed at the end of the output report for the salary:
![rowwise grandtotal in oracle11g rowwise grandtotal in oracle11g](http://4.bp.blogspot.com/-8gACr3FmxiA/Ugw3pxg_5BI/AAAAAAAABBo/m4tlBLTUT60/s1600/sumrule.jpg)
To produce a final total, use the following syntax: To obtain a grand total the SELECT contains a WITH to identify the column on which to perform an aggregate.īecause it is called a total, the SUM function is normally used, however, the other aggregates may also be used to generate a final value. At the same time, it shows the detail values, row by row, that went into the total. You may want to consider turning your function into a procedure that returns a cursor for the query above or perhaps have a function that returns the results of the query as a pipelined results set.Teradata provides for the creation and display of a final total for all the data values in one or more columns. Also, calling the function for every agent would be considerably less efficient than calling one SQL statement for all agents if your application can be designed in that manner. Of course as soon as you call the function from a different session the sum would be calculated again. Then you could reference the stored sum in your get commission function and it would only have to do the sum once. When your session is first created and calls the function in the package to get the commission there is an implicit call to the packages constructor which could get the sum and store it. If this is the case, then the package solution may help, but it depends on how your application works. Using a package to store the Commission SUM would involve PL/SQL, which you specifically excluded by indicating that you want a SQL solution, but since you are already using functions I assume your intention was not to exclude PL/SQL. To return all agents with their commissions and commission percentages use an analytic function with no analytic clause so that the partition is over the whole table: SELECT Agent, commission, 100* commission / (SUM(commission) OVER ()) "% Commission"Īs I learned from René Nyffenegger (+1) the ratio_to_report function tightens this syntax. I am limited to using SQL, and I'm running on Oracle 10g R2. I was thinking something on the lines of a 2 part query, the first part would fetch the sum(commission) into a package variable/type and the second part would refer to this pre-calculated value, but I'm not sure how I can accomplish this. Is there a way of having the Sum(Commission) not calculate for every row being fetched ? The problem is that Sum(commission) gets calculated for each and every row, and given that this query would be run on a Data Warehouse, the data set would be rather large ( currently, it's just under 2000 records) and quite honestly, a bad approach (IMO). I have another function returning the percentage as (Commission/Sum(Commission))*100. I have a function returning the commission for each agent. So, my expected data would be Agent | Commission | % Commission So, for Agent Smith, the Percentage would be calculated as (Agent Smith's commission / Sum(commission)*100 I need to calculate the percentage of the total commission, each agent is responsible for. This is currently (simplified view of the) data I'm working with Agent | Commission Apologies for the bad title, I wasn't sure what would be a good title for this.