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  |
sasisan09
Messages: 4
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 #577196 is a reply to message #577193] |
Wed, 13 February 2013 05:25  |
 |
Michel Cadot
Messages: 54194
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 |
sasisan09
Messages: 4
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 |
|
Michel Cadot
Messages: 54194
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
|
|
|
|
|
|
| Re: Row with Subtotal [message #577225 is a reply to message #577220] |
Wed, 13 February 2013 09:46 |
Solomon Yakobson
Messages: 1398
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.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed May 22 12:20:49 CDT 2013
Total time taken to generate the page: 0.17390 seconds
|
] 
Members
Search
Help
Register
Login
Home
