Home » SQL & PL/SQL » SQL & PL/SQL » Row with Subtotal (Oracle 11g, 11.2.0.3)
Row with Subtotal [message #577191] Wed, 13 February 2013 04:24 Go to next message
sasisan09
Messages: 9
Registered: October 2010
Location: India
Junior Member
Hi All,

I would want to have a sub total row with the sum of all the records that are returned from the query. There is possibility of duplicate records, so I cannot go with Group by Roll up straight away. And this could be achieved by querying table twice one to get the individual records and another with the sum() records. I have tried the following, please let me know if there is any better approach for achieving it.

create table tempTable1 (transactionType varchar2(20),amount number(9), date_time date );

begin
  insert into tempTable1 values ('Cash Payment', 200, trunc(sysdate));
  insert into tempTable1 values ('Card Payment', 200, trunc(sysdate));
  insert into tempTable1 values ('Cash Payment', 600, trunc(sysdate));
  insert into tempTable1 values ('Card Payment', 300, trunc(sysdate));
  insert into tempTable1 values ('Payment', 200, trunc(sysdate));
  commit;
end;

--Option1 :
select nvl(transactionType, 'Total'), date_time, sum(amount)
  from (select rownum, transactionType, amount, date_time from tempTable1)
 group by Grouping sets((rownum, transactionType, date_time),(1));

--Option 2: 
 select transactionType, amount, date_time
   from tempTable1
 union
 select 'Total', sum(amount), null from tempTable1;
 


Many Thanks in Advance!!!
Re: Row with Subtotal [message #577193 is a reply to message #577191] Wed, 13 February 2013 05:18 Go to previous messageGo to next message
Michel Cadot
Messages: 59751
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand what you want to have.
What does mean "There is possibility of duplicate records"? Duplicates in which meaning?
Please post the result you want for the data you gave.

Regards
Michel
Re: Row with Subtotal [message #577196 is a reply to message #577193] Wed, 13 February 2013 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59751
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, if date is important in your result then post a test case with different dates otherwise you will have "solutions" that will not fit your needs.
If "duplicates" are important then post test case and result with these "duplicates".
Explain why you can't use "rollup".

Regards
Michel

[Updated on: Wed, 13 February 2013 05:26]

Report message to a moderator

Re: Row with Subtotal [message #577201 is a reply to message #577196] Wed, 13 February 2013 06:20 Go to previous messageGo to next message
sasisan09
Messages: 9
Registered: October 2010
Location: India
Junior Member
Sure Michel,

First of all Thanks for the response.

What I meant by "There is possibility of duplicate records" was that we can have multiple records with the combination of transactionType, Amount, and date_time in the tempTable1.

And my requirement was to get individual records from the table on the specific date as well as a row that would hold the total amount on the day. i.e., if the 5 records in the inserts (mentioned in original post) were having same date_time, and we run the queries for that specific date, I would want my query to output all the 5 records matching the date and 1 total record that would give me the sum of the amounts on that date.

Output Expected :
Cash Payment	2/13/2013	600
Cash Payment	2/13/2013	200
Card Payment	2/13/2013	200
Payment	        2/13/2013	200
Card Payment	2/13/2013	300
Total		               1500


Date is just one of the conditions on which i need to filter the records from table. Updated queries are below :

--Option1 :
select nvl(transactionType, 'Total') Tran_Type, date_time DT, sum(amount) AMOUNT
  from (select rownum, transactionType, amount, date_time
          from tempTable1
         where date_time = trunc(sysdate))
 group by Grouping sets((rownum, transactionType, date_time),(1));
 
--Option 2: 
select transactionType Tran_Type,  date_time dt, amount Amount
  from tempTable1
 where date_time = trunc(sysdate)
union
select 'Total', null, sum(amount)
  from tempTable1
 where date_time = trunc(sysdate);



Thank you!!

Regards
Sasi
Re: Row with Subtotal [message #577215 is a reply to message #577201] Wed, 13 February 2013 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 59751
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think I understand now:
SQL> select nvl(transactionType, 'Total') Tran_Type, date_time DT, sum(amount) AMOUNT
  2  from tempTable1
  3  where date_time = trunc(sysdate)
  4  group by Grouping sets((rowid,transactionType, date_time),())
  5  /
TRAN_TYPE            DT                      AMOUNT
-------------------- ------------------- ----------
Cash Payment         13/02/2013 00:00:00        200
Card Payment         13/02/2013 00:00:00        200
Cash Payment         13/02/2013 00:00:00        600
Card Payment         13/02/2013 00:00:00        300
Payment              13/02/2013 00:00:00        200
Total                                          1500

SQL> select nvl(transactionType, 'Total') Tran_Type, date_time DT, sum(amount) AMOUNT
  2  from tempTable1
  3  where date_time = trunc(sysdate)
  4  group by rollup (rowid,transactionType, date_time)
  5  having grouping_id(rowid,transactionType, date_time) in (0,7)
  6  /
TRAN_TYPE            DT                      AMOUNT
-------------------- ------------------- ----------
Cash Payment         13/02/2013 00:00:00        200
Card Payment         13/02/2013 00:00:00        200
Cash Payment         13/02/2013 00:00:00        600
Card Payment         13/02/2013 00:00:00        300
Payment              13/02/2013 00:00:00        200
Total                                          1500

Regards
Michel
icon14.gif  Re: Row with Subtotal [message #577220 is a reply to message #577215] Wed, 13 February 2013 09:02 Go to previous messageGo to next message
sasisan09
Messages: 9
Registered: October 2010
Location: India
Junior Member
Michel, Thank you once again!! Appreciate your help Smile

Regards,
Sasi
Re: Row with Subtotal [message #577225 is a reply to message #577220] Wed, 13 February 2013 09:46 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2062
Registered: January 2010
Senior Member
Another option:

 select  transactionType Tran_Type,
         date_time DT,
         amount
   from  tempTable1
   where date_time = trunc(sysdate)
union all
 select  'Total',
         null,
         sum(amount)
   from  tempTable1
   where date_time = trunc(sysdate)
order by dt nulls last
/

TRAN_TYPE            DT            AMOUNT
-------------------- --------- ----------
Cash Payment         13-FEB-13        200
Card Payment         13-FEB-13        200
Cash Payment         13-FEB-13        600
Card Payment         13-FEB-13        300
Payment              13-FEB-13        200
Total                                1500

6 rows selected.

SQL> 


SY.
Re: Row with Subtotal [message #577296 is a reply to message #577225] Thu, 14 February 2013 01:25 Go to previous messageGo to next message
sasisan09
Messages: 9
Registered: October 2010
Location: India
Junior Member
Thanks Solomon!!

Considering performance, I noticed that suggestions from you and Michel are taking 370 secs and 360 secs respectively on 0.1Mn records that I tested. Both of them are better than the query I initially used. I will try to work on improving the performance further and come back to you in case I run out of options. Will update with my findings! Smile

Re: Row with Subtotal [message #577298 is a reply to message #577296] Thu, 14 February 2013 01:30 Go to previous message
Michel Cadot
Messages: 59751
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thank for the feedback, we will wait for your conclusions.

Regards
Michel
Previous Topic: SEQUENCE VALUE
Next Topic: Extracted data is displayed in wrong format
Goto Forum:
  


Current Time: Sun Nov 23 15:30:23 CST 2014

Total time taken to generate the page: 0.08794 seconds