Home » SQL & PL/SQL » SQL & PL/SQL » Analytical Function Sum(distinct value) (10g)
Analytical Function Sum(distinct value) [message #361813] 
Fri, 28 November 2008 02:38 
palhello
Messages: 15 Registered: November 2008

Junior Member 


I have the following scenraio
CREATE TABLE avgtable(id VARCHAR2(10),val NUMBER(10))NOLOGGING;
INSERT INTO avgtable SELECT 'A', 100 FROM DUAL;
INSERT INTO avgtable SELECT 'A', 100 FROM DUAL;
INSERT INTO avgtable SELECT 'B', 200 FROM DUAL;
INSERT INTO avgtable SELECT 'B', 200 FROM DUAL;
INSERT INTO avgtable SELECT 'C', 100 FROM DUAL;
INSERT INTO avgtable SELECT 'C', 100 FROM DUAL;
INSERT INTO avgtable SELECT 'D', 100 FROM DUAL;
INSERT INTO avgtable SELECT 'D', 100 FROM DUAL;
INSERT INTO avgtable SELECT 'D', 100 FROM DUAL;
INSERT INTO avgtable SELECT 'D', 100 FROM DUAL;
INSERT INTO avgtable SELECT 'E', 300 FROM DUAL;
160
SELECT Avg(val)
FROM
(
SELECT Sum(DISTINCT val) val,id FROM avgtable GROUP BY id
);
The output I get is 160 which is correct. However is there any way I can do it in one shot without using the subquery. Any help on such analytical function that would solve this would be great.
Thanks







Re: Analytical Function Sum(distinct value) [message #361893 is a reply to message #361846] 
Fri, 28 November 2008 07:58 
palhello
Messages: 15 Registered: November 2008

Junior Member 


I have the following scenario:
DROP TABLE avgtable;
CREATE TABLE avgtable(id VARCHAR2(10),val NUMBER(10),flg1 VARCHAR2(1),flg2 VARCHAR2(1))NOLOGGING;
INSERT INTO avgtable SELECT 'A', 100,'N','Y' FROM DUAL;
INSERT INTO avgtable SELECT 'A', 100,'N','Y' FROM DUAL;
INSERT INTO avgtable SELECT 'B', 200,'N','Y' FROM DUAL;
INSERT INTO avgtable SELECT 'B', 200,'N','Y' FROM DUAL;
INSERT INTO avgtable SELECT 'C', 100,'Y','N' FROM DUAL;
INSERT INTO avgtable SELECT 'C', 100,'Y','N' FROM DUAL;
INSERT INTO avgtable SELECT 'D', 100,'Y','N' FROM DUAL;
INSERT INTO avgtable SELECT 'D', 100,'Y','N' FROM DUAL;
INSERT INTO avgtable SELECT 'D', 100,'Y','N' FROM DUAL;
INSERT INTO avgtable SELECT 'D', 100,'Y','N' FROM DUAL;
INSERT INTO avgtable SELECT 'E', 300,'Y','N' FROM DUAL;
correct value of
SELECT Count(CASE WHEN flg1 = 'Y' THEN 1 ELSE NULL END) output1,
Count(CASE WHEN flg2 = 'Y' THEN 1 ELSE NULL END) output2,
Sum(CASE WHEN flg1 = 'Y' THEN val ELSE 0 END) output3
FROM
avgtable;
incorrect value
SELECT Count(CASE WHEN flg1 = 'Y' THEN 1 ELSE NULL END) output1,
Count(CASE WHEN flg2 = 'Y' THEN 1 ELSE NULL END) output2,
Sum(CASE WHEN flg1 = 'Y' THEN val ELSE 0 END) output3,
avg(Sum(DISTINCT val))
FROM avgtable GROUP BY id,flg1,flg2,val;
As you can see that if I try to incorporate the average in the same query then the "output1","output2","output3" comes incorrect,so I need some analytic function to do this.




Re: Analytical Function Sum(distinct value) [message #361899 is a reply to message #361897] 
Fri, 28 November 2008 08:33 
palhello
Messages: 15 Registered: November 2008

Junior Member 


Sorry for being unclear. Here is the scenario
Query1
SELECT Count(CASE WHEN flg1 = 'Y' THEN 1 ELSE NULL END) output1,
Count(CASE WHEN flg2 = 'Y' THEN 1 ELSE NULL END) output2,
Sum(CASE WHEN flg1 = 'Y' THEN val ELSE 0 END) output3
FROM
avgtable;
Output1 = 7
Output2 = 4
Output3 = 900
This is the result that I desire.
If I try to implement your way of Average I will have to put group by clause and the query becomes this
Query2
SELECT Count(CASE WHEN flg1 = 'Y' THEN 1 ELSE NULL END) output1,
Count(CASE WHEN flg2 = 'Y' THEN 1 ELSE NULL END) output2,
Sum(CASE WHEN flg1 = 'Y' THEN val ELSE 0 END) output3,
avg(Sum(DISTINCT val))
FROM avgtable GROUP BY id,flg1,flg2,val;
Output1 = 3
Output2 = 2
Output3 = 500
So, I was wondering if I could get the avg. of query2 in query1. If it is not possible with analytical function then that's even great with plain sql.
Thanks.





Re: Analytical Function Sum(distinct value) [message #361914 is a reply to message #361907] 
Fri, 28 November 2008 09:37 
palhello
Messages: 15 Registered: November 2008

Junior Member 


Well, to explain it further, I wanted to do something like this :
1. Count the values which have flg1 = 'Y'
2. Count the values which have flg2 = 'Y'
3. Sum up all the values that have flg2 = 'Y'
4. Find the Average "Val" for all ID. If the ID is repeated more than once, we have to take any one of the "Val" and find the average. So, the numerator, denominator of the average will be like this
Avg = Sum(single value of each id)/Count(Distinct ID)
I want to achieve all of these four requirements from a single query, because the table that I am going to query it is pretty big around 6 mil. rows.
If it cannot be done by analytical functions (or if you think they are not suitable for this requirement) any other sql way of doing it is highly appreciable.
Thanks.




Re: Analytical Function Sum(distinct value) [message #361929 is a reply to message #361914] 
Fri, 28 November 2008 10:46 
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom

Senior Member 


Probably this is what you are after. It is only an extension of logic given by @Michael already.
SELECT Count(CASE WHEN flg1 = 'Y' THEN 1 ELSE NULL END) output1,
Count(CASE WHEN flg2 = 'Y' THEN 1 ELSE NULL END) output2,
Sum(CASE WHEN flg1 = 'Y' THEN val ELSE 0 END) output3,
avg(distinct val)
FROM avgtable GROUP BY rollup(id)
having grouping_id(id) = 1;
Regards
Raj




Re: Analytical Function Sum(distinct value) [message #361955 is a reply to message #361813] 
Fri, 28 November 2008 13:19 
palhello
Messages: 15 Registered: November 2008

Junior Member 


Quote: 
If you want ot know a little more about anlyatics, try tnis:
A Simple Example of Oracle Analytics: Running Totals
Kevin Meade's blog
Good luck, Kevin

Thanks for the useful links, I will certainly dig them up.
Quote: 
Probably this is what you are after. It is only an extension of logic given by @Michael already.
SELECT Count(CASE WHEN flg1 = 'Y' THEN 1 ELSE NULL END) output1,
Count(CASE WHEN flg2 = 'Y' THEN 1 ELSE NULL END) output2,
Sum(CASE WHEN flg1 = 'Y' THEN val ELSE 0 END) output3,
avg(distinct val)
FROM avgtable GROUP BY rollup(id)
having grouping_id(id) = 1;

I tried it and it gives the result as
OUTPUT1 OUTPUT2 OUTPUT3 AVG(DISTINCTVAL)
7 4 900 200
which is not desirable to to the AVG field which is 200 ?
Quote: 
And so for the data you gate the result should be?

So, for the above sample date, I need to get the following result
OUTPUT1 OUTPUT2 OUTPUT3 AVG(DISTINCTVAL)
7 4 900 160
Thanks






Goto Forum:
Current Time: Mon Jan 23 05:16:40 CST 2017
Total time taken to generate the page: 0.30024 seconds
