Solved

Aggregate

Posted on 2014-10-22
3
91 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
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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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ā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now