Skip to Main Content
  • Questions

  • Grand Total and Group Totals in Top-N

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, x.

Asked: December 12, 2005 - 11:31 am UTC

Last updated: December 14, 2005 - 12:56 pm UTC

Version: 9i

Viewed 1000+ times

You Asked

I have a query that returns top-5 Products for each Store based on Sales(aggregated). Query looks something like-

SELECT * FROM (
SELECT StoreName,ProductName,SUM(Sales),DENSE_RANK() over(
partition by StoreName order by
SUM(Sales) desc) Rank
FROM **********
WHERE *********
GROUP BY StoreName, ProductName
)
where Rank <=5;

It works fine. What I want to do now is to return another column for the GrandTotal of sales (not limited to top-5) for each Store. Of course, it would mean that for every store there would be 5 values which are the same (grand total). But, I am fine with that.

Is it possible to do this in the same query?

and Tom said...

this does the top two since my emp table doesn't have a top-5 and I found the question interesting enough to answer it without making you supply the create table/insert intos for the test case :)


scott@ORA9IR2> select deptno, decode( btn, 0, job, 'tot===>') job, sum_sal, rank, btn
2 from (
3 select deptno, job,
4 sum(sal) sum_sal, dense_rank() over (partition by deptno order by sum(sal)) rank,
5 bin_to_num( grouping(deptno), grouping(job) ) btn
6 from emp
7 group by rollup( deptno, job )
8 )
9 where (btn = 0 and rank <= 2) or (btn=1)
10 /

DEPTNO JOB SUM_SAL RANK BTN
---------- --------- ---------- ---------- ----------
10 CLERK 1300 1 0
10 MANAGER 2450 2 0
10 tot===> 8750 4 1
20 CLERK 1900 1 0
20 MANAGER 2975 2 0
20 tot===> 10875 4 1
30 CLERK 950 1 0
30 MANAGER 2850 2 0
30 tot===> 9400 4 1

9 rows selected.


Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great!! But how about Total as a column?

xyz, December 12, 2005 - 2:35 pm UTC

That was an awesome query. How would you tweak your query if the GrandTotal (tot===>) were to appear as a field and not records? This would result in a table such as-
DEPTNO JOB SUM_SAL RANK BTN GrandTot
---------- --------- ---------- ---------- ----- --------
10 CLERK 1300 1 0 8750
10 MANAGER 2450 2 0 8750
20 CLERK 1900 1 0 10875
20 MANAGER 2975 2 0 10875
30 CLERK 950 1 0 9400
30 MANAGER 2850 2 0 9400




One solution

Michel Cadot, December 12, 2005 - 2:59 pm UTC 

Hi,

Here's a solution:

SQL> select deptno, job, sum_sal,
  2         dense_rank() over (partition by deptno order by sum_sal) rank,
  3         sum(sum_sal) over 
  4           (partition by deptno order by job 
  5            rows between unbounded preceding and unbounded following)
  6           grandtotal
  7  from ( select deptno, job, sum(sal) sum_sal
  8         from emp
  9         group by deptno, job )
 10  /
    DEPTNO JOB          SUM_SAL       RANK GRANDTOTAL
---------- --------- ---------- ---------- ----------
        10 CLERK           1300          1       8750
        10 MANAGER         2450          2       8750
        10 PRESIDENT       5000          3       8750
        20 CLERK           1900          1      10875
        20 MANAGER         2975          2      10875
        20 ANALYST         6000          3      10875
        30 CLERK            950          1       9400
        30 MANAGER         2850          2       9400
        30 SALESMAN        5600          3       9400

9 rows selected.

Regards
Michel

Yeah...Kudos Michel

xyz, December 12, 2005 - 3:14 pm UTC

Yeah, that's a cool solution. Haven't checked out the performance though. My query is a lot more complex; involves a lot of tables,with a number of groupings, etc. I hope the suggestions here will provide the skeleton for my solutions. I will keep you posted if things don't move in the right direction.
Cheers to Michel and askTom group.

Tom Kyte
December 12, 2005 - 4:17 pm UTC

you don't need the extra layer, when you compute dense_rank, you are looking at all of the rows in the partition after grouping - you can just get the total then too:


scott@ORA9IR2> select deptno, job, sum_sal, tot_sal, rank
2 from (
3 select deptno, job,
4 sum(sal) sum_sal,
5 dense_rank() over (partition by deptno order by sum(sal)) rank,
6 sum( sum(sal) ) over (partition by deptno ) tot_sal
7 from emp
8 group by deptno, job
9 )
10 where rank <= 2
11 /

DEPTNO JOB SUM_SAL TOT_SAL RANK
---------- --------- ---------- ---------- ----------
10 CLERK 1300 8750 1
10 MANAGER 2450 8750 2
20 CLERK 1900 10875 1
20 MANAGER 2975 10875 2
30 CLERK 950 9400 1
30 MANAGER 2850 9400 2

6 rows selected.



Good things come in BIG packages??

xyz, December 12, 2005 - 4:42 pm UTC

So, good things do come in bulk. Yeah, I have had a number of good suggestions for my question. I hope it helps others out there as well. Thanks guys. I really appreciate it. And all the while I thought good things only come in small packages. Also, remember Shakespeare--- he said, "When troubles come, they come not single spies, but in batallions". For the time being "Off with you, S'peare"

Did you mean this?

Ofir Manor, December 12, 2005 - 6:34 pm UTC

Hi Tom,
a quick one:
you used the expression:
bin_to_num( grouping(deptno), grouping(job) ) btn
You could use the builtin version:
grouping_id(deptno,job) btn
I thought it is worth a mention...
Ofir
PS - I'll risk being too much a nitpicker:
It really doesn't matter in this case, but the rollup does one extra aggregation level which is not needed (grand total), and is filtered in the outer query.
It is generally possible to skip the unneeded aggregation levels. In this case there are two options:
1. Specify the exact aggregation levels:
GROUP BY GROUPING SETS((dept_no,job),(dept_no))
1.Just use partial rollup:
GROUP BY dept_no,ROLLUP(job)
analytics are fun (-:

QUERIES

RG, December 14, 2005 - 10:37 am UTC

I have to create and save a query that list the grand total number of orders, grand totals for the total invoice, amount paid fields for each customer. I have to change the column name and it should be modified: add a new column.

How do you do this????

Tom Kyte
December 14, 2005 - 12:56 pm UTC

er? cannot really visualize what you mean, especially when you say "add a new column".

hows about an example.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.