Home » SQL & PL/SQL » SQL & PL/SQL » aggregate functions
aggregate functions [message #193817] Tue, 19 September 2006 09:34 Go to next message
pfloyd_mtl
Messages: 4
Registered: September 2006
Junior Member
Trying to do something very simple but not getting the expected results. The table is relatively simple, it counts the number of transactions done by users each day.

So the table looks like this (pseudo-code)

- User
- Service
- Transaction
- Total

Example

User X on Service Y did 3 different type of Transactions (P,Q,R) yesterday. The table would contain 3 rows

X, Y, P, 10
X, Y, Q, 5
X, Y, R, 2

Let's add another user to this example

T, Y, P, 8
T, Y, Q, 12
T, Y, R, 3

What I'm trying to do is provide user stats for service Y.

- Number of Users that did Transactions P,Q,R -> 2
- Number of Transactions P,Q,R -> 40
- Average per User -> 40/2
- Variance per User -> n
- Maximum Transactions P,Q,R per User -> 23

When I do the obvious aggregate functions to get these values, I don't seem to get what I expect.

select service,
count(distinct users),
sum(total),
avg(total),
variance(total),
max(total)
from table
where transaction in ('P', 'Q', 'R')
group by service;

The count and sum values are correct but the avg, variance and max don't jive... Any ideas?

--Marc.
Re: aggregate functions [message #193822 is a reply to message #193817] Tue, 19 September 2006 09:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Go on, give us a clue - how exactly are the values wrong?
Re: aggregate functions [message #193828 is a reply to message #193822] Tue, 19 September 2006 10:26 Go to previous messageGo to next message
pfloyd_mtl
Messages: 4
Registered: September 2006
Junior Member
avg(total) calculates the average for total in each row but what I need is the average per user.

In my examples, user X did 17 transactions and user Y did 23 transactions. The average is 17+23/2 so 20... What I get is 10+5+2+8+12+3/6 so 6.66

Same problem with variance and max, I need these stats per user and not per row... max(total) gives me 12 but I want 23!
Re: aggregate functions [message #193831 is a reply to message #193828] Tue, 19 September 2006 10:35 Go to previous messageGo to next message
pfloyd_mtl
Messages: 4
Registered: September 2006
Junior Member
Basically what I need is nested aggregates but Oracle doesn't like that! The statements I'm looking for would be something like:

avg(sum(total)),
variance(sum(total)),
max(sum(total))

where sum(total) is the number of transactions for that user!
Re: aggregate functions [message #193845 is a reply to message #193831] Tue, 19 September 2006 12:12 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Then group by the USER.
Re: aggregate functions [message #193854 is a reply to message #193817] Tue, 19 September 2006 13:01 Go to previous message
pfloyd_mtl
Messages: 4
Registered: September 2006
Junior Member
I found a solution after many attempts... Not sure if it's the best one but it does provide the right results...

select service,
count(*) "COUNT",
sum(total2) "SUM",
trunc(avg(total2),2) "AVG",
trunc(variance(total2),2) "VAR",
max(total2) "MAX"
from
(select service, users, sum(total) "TOTAL2"
from table
where event in ('P', 'Q', 'R')
group by service, users)
group by service;

Now I just need to figure out how to list the users that do more transactions than AVG+VAR! Rolling Eyes
Previous Topic: function changes not recognised
Next Topic: Oracle 8i installation problems.
Goto Forum:
  


Current Time: Sun Dec 11 00:26:37 CST 2016

Total time taken to generate the page: 0.07293 seconds