First, I am finding an average of some data, and then formatting that average into to decimal points. And in the end, I want to use rollup to generate a total row for all columns.
The problem is: I want the rollup to sum the data as they appear, however, when adding up the average it is not adding them as the way they were formatted, but rather their full actual values, and I don’t want that.
for example if the average is
25.66666667 and the formatted number is shown as 26.67
AND 10.5192 and the formatted number is shown as 10.52
I want the roll up to add: 26.67 + 10.52, NOT 25.66666667 + 10.5192
Any idea how using Oracle SQL? Or any alternatives for roll ups that would give me the required result. Note that I need to generate the total summary row during my sql query command.
Advertisement
Answer
Welcome A. Nassar to stack overflow. It helps a lot if you provide a minimal reproducible case when you post a question. It will help you better understand your problem and it makes it a lot easier for others to help you.
But you didn’t do that so here is an example on the sample dataset emp/dept. If you don’t have it in your database, you easily can find it on the web or on livesql.oracle.com
To get the rounded numbers, use the ROUND function.
Oracle has a ROLLUP function to do exactly what you want. But doing a rollup on a query that has an AVG aggregate function will do an AVG of the other averages. That is not what you want. You want a SUM of AVG. That can be achieved by doing the AVG calculation in a CTE (Common Table Expression – that “WITH” clause) and then doing a sum on those averages. The SUM won’t actually do anything because it sums only a single value but it will allow the ROLLUP to do a sum. Putting it all together gives:
WITH avgs (deptno, avg_sal)
AS
(
SELECT d.deptno,
ROUND(AVG(e.sal),2)
FROM emp e
JOIN dept d ON e.deptno = d.deptno
GROUP BY d.deptno
)
SELECT deptno, SUM(avg_sal)
FROM avgs
GROUP BY ROLLUP(deptno);
DEPTNO SUM(AVG_SAL)
---------- ------------
10 2916.67
20 2175
30 1566.67
6658.34