CODEWITHSUNDEEP
oracle-databasesumgrouping
Landon Statis
Landon Statis

Reputation: 839

Oracle Grand Total

I am having a query where I am selecting 4 columns. And, I want to put a grand total at the bottom of one of the columns, and not do any grouping:

SELECT customer_id, email, total_amount, order_date
FROM...................

I want to do a grand total of TOTAL_AMOUNT at the bottom, but not worry about any grouping. I'm not seeing how to do this using GROUPING or ROLLUP. I'm hoping not to have this as any running total in another oolumn, but as a grand total at the bottom.

Many thanks.

Upvotes: 1

Views: 254

Answers (2)

Jon Heller
Jon Heller

Reputation: 36807

You can add a grand total row with a UNION ALL and a column to track if the row is for the grand total.

select customer_id, email, total_amount, order_date, 0 is_grand_total
from orders
union all
select null, null, sum(total_amount), null, 1 is_grand_total
from orders
order by is_grand_total, customer_id;

SQL Fiddle example.

(In my opinion, this is often a good way to add summary logic to queries. I'd rather have a slightly more complicated solution with one language (SQL), than a solution that involves two or more languages or applications.)

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142713

A simple SQL*Plus option:

SQL> break on report
SQL> compute sum of sal on report
SQL>
SQL> select deptno, ename, sal
  2  from emp
  3  where deptno = 10;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK            2450
        10 KING             5001
        10 MILLER           1300
                      ----------
sum                         8751

SQL>

Upvotes: 0

Related Questions