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 Go to next message
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 sub-query. Any help on such analytical function that would solve this would be great.

Thanks
Re: Analytical Function Sum(distinct value) [message #361816 is a reply to message #361813] Fri, 28 November 2008 02:57 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Tried this in oracle 10g, but not sure if you mean it this way..


Select ID, AVG (distinct NUM_COL) from NUM_TABLE group by ID



Thanks,
Wilbert

[Updated on: Fri, 28 November 2008 03:10]

Report message to a moderator

Re: Analytical Function Sum(distinct value) [message #361823 is a reply to message #361813] Fri, 28 November 2008 03:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> SELECT avg(Sum(DISTINCT val)) FROM avgtable GROUP BY id;
AVG(SUM(DISTINCTVAL))
---------------------
                  160

1 row selected.

Regards
Michel
Re: Analytical Function Sum(distinct value) [message #361837 is a reply to message #361823] Fri, 28 November 2008 03:59 Go to previous messageGo to next message
palhello
Messages: 15
Registered: November 2008
Junior Member
Is there a way to do it without Group by and using analytical query ? I need a cumulative result hence grouping it wouldn't suffice the need. Neither putting the whole query in a subquery.


Thanks.

[Updated on: Fri, 28 November 2008 04:00]

Report message to a moderator

Re: Analytical Function Sum(distinct value) [message #361846 is a reply to message #361837] Fri, 28 November 2008 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you EXACTLY need.
The provided query give the expected result, doesn't it?

Regards
Michel
Re: Analytical Function Sum(distinct value) [message #361893 is a reply to message #361846] Fri, 28 November 2008 07:58 Go to previous messageGo to next message
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 #361897 is a reply to message #361893] Fri, 28 November 2008 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand anything. Explain with WORDS what is the requirement (and stop saying you want analytics, why analytics?).

Regards
Michel
Re: Analytical Function Sum(distinct value) [message #361899 is a reply to message #361897] Fri, 28 November 2008 08:33 Go to previous messageGo to next message
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 #361907 is a reply to message #361899] Fri, 28 November 2008 09:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't want queries just words saying what are the requirements.
When your boss gives you a job, he doesn't give you queries, does it?

Regards
Michel
Re: Analytical Function Sum(distinct value) [message #361912 is a reply to message #361907] Fri, 28 November 2008 09:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You haven't worked some of the places I have.
In some of them the job spec came with the SQL written in it.
Re: Analytical Function Sum(distinct value) [message #361914 is a reply to message #361907] Fri, 28 November 2008 09:37 Go to previous messageGo to next message
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 #361923 is a reply to message #361914] Fri, 28 November 2008 10:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And so for the data you gate the result should be?

Regards
Michel
Re: Analytical Function Sum(distinct value) [message #361929 is a reply to message #361914] Fri, 28 November 2008 10:46 Go to previous messageGo to next message
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 #361948 is a reply to message #361813] Fri, 28 November 2008 12:07 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
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
Re: Analytical Function Sum(distinct value) [message #361955 is a reply to message #361813] Fri, 28 November 2008 13:19 Go to previous messageGo to next message
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
Re: Analytical Function Sum(distinct value) [message #361958 is a reply to message #361955] Fri, 28 November 2008 13:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select sum(output1) output1,
  2         sum(output2) output2,
  3         sum(output3) output3,
  4         avg(output4) output4
  5  from (
  6  SELECT Count(CASE WHEN flg1 = 'Y' THEN 1 ELSE NULL END) output1,
  7         Count(CASE WHEN flg2 = 'Y' THEN 1 ELSE NULL END) output2,
  8         Sum(CASE WHEN flg1 = 'Y' THEN val ELSE 0 END) output3,
  9         avg(val) output4
 10  FROM avgtable
 11  group by id
 12  )
 13  /
   OUTPUT1    OUTPUT2    OUTPUT3    OUTPUT4
---------- ---------- ---------- ----------
         7          4        900        160

1 row selected.

Regards
Michel
Re: Analytical Function Sum(distinct value) [message #361969 is a reply to message #361958] Fri, 28 November 2008 21:35 Go to previous messageGo to next message
palhello
Messages: 15
Registered: November 2008
Junior Member
Thanks, that does the trick. However if you look at the first post I had a subquery similar to that ( probably not optimized though Smile ) .

Is there a way to do this *without* the subquery ? I am emphasizing on without the subquery because I have to change around long (200 lines) queries.

Anyways, if there is no other way I will have to change all the queries the way you suggest. And I would have learned the hard way that I should have followed the requirements strictly.



Thanks.

Re: Analytical Function Sum(distinct value) [message #361974 is a reply to message #361969] Sat, 29 November 2008 00:21 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Given your data, no other way.

Regards
Michel
Previous Topic: Re: ORA-13774: insufficient privileges to select data from the workload repository
Next Topic: Query with Timestamp and aggregate Function
Goto Forum:
  


Current Time: Sun Dec 04 08:22:59 CST 2016

Total time taken to generate the page: 0.20256 seconds