Solved

Aggregate

Posted on 2014-10-22
3
89 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
Comment Utility
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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

771 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

11 Experts available now in Live!

Get 1:1 Help Now