Home » SQL & PL/SQL » SQL & PL/SQL » Deviation of monthly totals?
Deviation of monthly totals? [message #358452] Tue, 11 November 2008 07:22 Go to next message
Messages: 1
Registered: November 2008
Junior Member
I'm trying to calculate the standard deviation of monthly amount of transactions. Currently something like
with sums as( select sum(qty) as q from inventory where itemid = 'xxx' and date > (sysdate-365)
group by extract(month from date), extract(year from date) )

select stddev(q) from sums;

works (my example is a little bit pseudo-codish because I didn't diretcly copy-paste it) but has s few problems. First, if we have a month with no transactions, this doesn't work because the first part doesn't return zeros for these months. The even bigger problem is that if I try to do something like
select z, y, (select ...), (select stddev(q) from (select sum(qty) ... where itemid = parts.id ...))
from parts where id = 'fhsadg';

it doesn't work. Do you have any ideas which would help me?

Re: Deviation of monthly totals? [message #358459 is a reply to message #358452] Tue, 11 November 2008 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 63911
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We have no idea what "it does not work" mean as we don't know what you're trying to do.

Post a test case: create table and insert statements along with the result you want with these data.

Re: Deviation of monthly totals? [message #358509 is a reply to message #358452] Tue, 11 November 2008 11:59 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
As for your issue of missing months, you can consider doing a DATA FILL operation. Generate a list of months using some variation of a number generator, and then join to it. If you need an example, try reading this:

The SQL WITH Clause, (Order Your Thoughts, Reuse Your Code)
A Simple Example of Oracle Analytics: Running Totals

Previous Topic: show % of tablespace correctly
Next Topic: Split Function for Input Blob
Goto Forum:

Current Time: Sat Oct 22 19:44:56 CDT 2016

Total time taken to generate the page: 0.08068 seconds