?
Solved

Calculating a rate over time

Posted on 2014-01-27
12
Medium Priority
?
305 Views
Last Modified: 2014-01-31
I am trying to calculate a rate over time.  I have two distinct tables in my database and need quantities from both tables.  Here is what I have so far which is not working:

SELECT ROUND(my_count/cumulative_amount*1000000,2) FROM
(SELECT SUM(val1) as my_count, fiscalMonth from tableA where flag=1 and fiscal_year=2013
 group by fiscalMonth
order by fiscalMonth),
(SELECT SUM(SUM(amount)) OVER
  (ORDER BY fiscalMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS
   cumulative_amount from tableB where fiscalYear = 2013 
group by fiscalmonth,
order by fiscalmonth)

Open in new window


Each of the queries running against Table A and Table B give the correct result.  How do I do the calculation?
Thanks and I hope this makes sense.
0
Comment
Question by:Breadbaker
[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
12 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39812952
please post sample data and expected results
0
 

Author Comment

by:Breadbaker
ID: 39813054
VALUE      CUMULATIVE VALUE2  RESULT
OCT      3452212      3452212             2         0.579338696
NOV      3556722      7008934             1         0.142675049
DEC      4328764      11337698             4         0.352805305
JAN      3676622      15014320             5         0.333015415
FEB      5003345      20017665             0         0
MAR      3452299      23469964             0         0
APR      4578234      28048198             5         0.178264572
MAY      5366325      33414523             7         0.209489748
JUN      3487690      36902213             1         0.027098646
JUL      3276843      40179056             2         0.049777177
AUG      4226612      44405668             6         0.135117886
SEP      3962247      48367915             5         0.103374313

RESULT is value2/cumulative*1000000 and that's the value I want to retrieve.  I can get the cumulactive and the value2  Hope this helps and thank you!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39813073
no, I'm sorry, it doesn't.


you have 2 tables in your query, but I only see one table's data

If that sample data is supposed to represent your current query, it doesn't make sense because your current query would only return one column

I don't understand what the sample data is supposed to represent

also, if you can generate the value2 and cumulative columns, and you know how to calculate the results.

I'm not sure what you're asking.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:Breadbaker
ID: 39813359
I want to generate a rate for each month as defined by count "divided by" cumulative rate for that month*1000000.  I can do one query to get the cumulative rate and another to get the count.  Each value is in a different table and there is no key/foreign key.

I was just wondering if one query could retrieve both values and do the calculation as well.  I can just use 2 queries and do the calculation in my coldfusion.  

My data table above is skewed a bit, sorry about that.

VALUE      CUMUL      VALUE2      RESULT
3452212      3452212      2      0.579338696   Result for each row is value2/CUMUL*1000000
3556722      7008934      1      0.142675049
4328764      11337698      4      0.352805305
3676622      15014320      5      0.333015415
5003345      20017665      0      0
3452299      23469964      0      0
4578234      28048198      5      0.178264572
5366325      33414523      7      0.209489748
3487690      36902213      1      0.027098646
3276843      40179056      2      0.049777177
4226612      44405668      6      0.135117886
3962247      48367915      5      0.103374313
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39813382
Your sample data still doesn't correspond to anything you've posted.
It is neither table data nor the result of a query you have posted.


or, if it does correspond to your previous query, please explain how.

where do value and value2 come from?


I'm assuming your "sample data" is really the "expected results"
but you haven't show the corresponding Input data that should produce that output.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39813678
Breadbaker,

What sdstuber (and the other Experts) are looking for is the actual data for the two tables and then the expected results.

For example:  From the original select you posted, you have:
tableA
--------
val1
fiscalMonth 
flag
fiscal_year

tableB
--------
amount
fiscalMonth 
fiscalmonth
fiscalYear
                                  

Open in new window


Please provide sample data for BOTH tables, then based on that data, provide what you expect the results to be.
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 1000 total points
ID: 39814153
there is a syntax error at line 8 of the original query (a trailing comma)
- so it would appear we are only seeing part of the full query
- & you should avoid using "order by" in those subqueries

The overall logic of that original query seems to be that you intend to join "tablea" to "tableb" via the field fiscalmonth, which is OK if the data is for only one fiscal year but then you are not actually specifying this relationship so you are probably getting a Cartesian product instead of the intended join by fiscalmonth to fiscalmonth.

This seems to be what you intend (I think):
SELECT ROUND(a.my_count / b.cumulative_amount * 1000000, 2)
     /* nb there is no protection against divide by zero error */
FROM (
	SELECT SUM(val1) AS my_count
		, fiscalMonth
	FROM tableA
	WHERE flag = 1 AND fiscal_year = 2013
	GROUP BY fiscalMonth
	) A
LEFT JOIN (
	SELECT fiscalMonth
		, SUM(amount) OVER (
			ORDER BY fiscalMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
			) AS cumulative_amount
	FROM tableB
	WHERE fiscalYear = 2013
	GROUP BY fiscalmonth
	) b ON a.fiscalMonth = b.fiscalMonth

Open in new window

The comments above about sample data are quite correct. The small query result you have provided does not aid us as it does not fit into tablea or tableb and hence we cannot trial a query using those tables.
0
 
LVL 32

Expert Comment

by:awking00
ID: 39815504
Can you just provide the results of the two following queries?

select fiscalmonth, val1 from tablea
where flag = 1 and fiscalyear = 2013;

select fiscalmonth, amount from tableb
where fiscalyear = 2013;
0
 

Author Comment

by:Breadbaker
ID: 39816177
Here is some sample data:
Table A
fiscal month      val1
1                     0
2                     0
3                     0
4                     0
5                     2
6                     1
7                     2
8                     3
9                     2
10                     0
11                     1
12                     2

Table B
fiscal month          amount                        
1                            4231112
2                   4366112
3                   4434465
4                   6542223
5                   3311222
6                   4332211
7                   4982311
8                   3422189
9                   3321114
10                   6200114
11                   3224568
12                   4216895

Result I want:
fiscal month          rate
1                 0
2                 0
3                 0
4                 0
5                 0.2
6                0.13
7                0.21
8               0.36
9               0.3
10               0
11               0.11
12              0.27

Rate calculated by dividing val1 by cumulative sum of the amount per month.  PortletPaul has very close to what I think I need and I am attempting to construct the query like that, I am just unfamiliar with the syntax for something like this.  Thank you.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39816254
your values don't seem to correspond to your description

5                 0.2

but your description says 0.2 would be calculated as

2 / (4231112 + 4366112 + 4434465 + 6542223 + 3311222)

which equals 0.0000000873929774673812266076309625279013004687

How are you getting 0.2  ?
0
 

Author Comment

by:Breadbaker
ID: 39818113
Sorry, you are correct.  That is what I want to do.  So the correct values for the accumulative values would be:
4231112
8597224
13031689
19573912
22885134
27217345
32199656
35621845
38942959
45143073
48367641
52584536

and the rates would be:
0
0
0
0
0.09  
0.04
0.06
0.08
0.05
0
0.02
0.04
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 39818146
thank you, given the 2 tables and the correct expected results, it's possible to build a test case and write the query.

This produces the requested rates in http:#a39818113 for the data given in http:#a39816177 

SELECT a.fiscalmonth, ROUND(a.val1 / SUM(b.amount) OVER (ORDER BY a.fiscalmonth) * 1000000, 2) rate
  FROM tablea a, tableb b
 WHERE a.fiscalmonth = b.fiscalmonth

Open in new window



I didn't need the cumulative results, the query generates those on its own.
Also, your last description didn't say anything about multiplying results by a million, but based on the values and previous posts I guessed you still wanted to do that
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

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

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

Join & Ask a Question