[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 100
  • Last Modified:

Aggregate

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
gs79
Asked:
gs79
1 Solution
 
Haris DjulicCommented:
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
 
Wasim Akram ShaikCommented:
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
 
sdstuberCommented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now