Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Aggregate

Posted on 2014-10-22
Medium Priority
96 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
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

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
``````
0

LVL 16

Expert Comment

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

sdstuber earned 2000 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.
``````
0

## Featured Post

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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 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
Course of the Month4 days, 16 hours left to enroll

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

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