Solved

Aggregate

Posted on 2014-10-22
3
95 Views
Last Modified: 2014-12-01
I have a data as follows. We have two set of records with different grain as shown below: when we have total amount used amount is blank.

id1        id2         id3       tot_amt     used_amt  
abc                                  100
abc        111       001                          25
abc        111       002                          50

now I need to calculate unused_amt which is difference of tot_amt and used amount (100 - 75) and populate in the same line as tot amt. Here is the output desired

id1        id2         id3       tot_amt     used_amt     unused_amt
abc                                  100                                    25
abc        111       001                          25
abc        111       002                          50

is there a way to do this?  Is there an aggregate function we can use?  please help

thanks
0
Comment
Question by:gs79
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40398673
Try this code:

select * from (
select id1, '' as id2, '' as id3, sum(tot_amt) as tot_amt, '' as used_amt, sum(tot_amt)-sum(used_amt) as unused_amt
from table_name
group by id1
union all
select id1, id2, id3, '' as tot_amt, sum(used_amt) as used_amt, '' as unused_amt
from table_name 
group by id1,id2, id3
)a
order by id1

Open in new window

0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40399005
Check the queries in sql fiddle..


select id1,id2,id3,tot_amt,used_amt,sum_tot_amt-sum_unused unused_amt from(
select id1,id2,id3,tot_amt,used_amt,
sum(tot_amt) over(partition by id1) sum_tot_amt,
sum(used_amt) over(partition by id1) sum_unused from mytable);


If you want to have unused_amout per record then use this

select id1,id2,id3,tot_amt,used_amt,
sum(tot_amt) over(partition by id1)-nvl(used_amt,0)  unused_amt from mytable;

check out both the query and their result sets in sql fiddle

http://www.sqlfiddle.com/#!4/f2142/30
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40399227
doesn't look to me like any of the queries above produce the requested results.  Even the result posted on sqlfiddle shows a wrong unused amount



Try this...


  SELECT id1,
         id2,
         id3,
         tot_amt,
         used_amt,
         CASE WHEN tot_amt IS NOT NULL THEN tot_amt - SUM(used_amt) OVER (PARTITION BY id1) END
             unused_amt
    FROM yourtable
ORDER BY id1, id2 NULLS FIRST, id3 NULLS FIRST;


ID1 ID2 ID3    TOT_AMT   USED_AMT UNUSED_AMT
--- --- --- ---------- ---------- ----------
abc                100                    25
abc 111 001                    25           
abc 111 002                    50           

3 rows selected.

Open in new window

0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question