StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You should be able to get everything you need in one go:</p> <pre><code> SELECT AVG(D.AMOUNT) as AMOUNT, Y.YEAR as YEAR, MAKEDATE(Y.YEAR,1) as AMOUNT_DATE, Y.YEAR * ymxb.SLOPE + ymxb.INTERCEPT as REGRESSION_LINE, ((avg(AVG(D.AMOUNT) * Y.YEAR)) - avg(AVG(D.AMOUNT)) * avg(Y.YEAR)) / (stddev( AVG(D.AMOUNT) ) * stddev( Y.YEAR )) as CORRELATION, ((sum(Y.YEAR) * sum(AVG(D.AMOUNT))) - (count(1) * sum(Y.YEAR * AVG(D.AMOUNT)))) / (power(sum(Y.YEAR), 2) - count(1) * sum(power(Y.YEAR, 2))) as SLOPE, ((sum( Y.YEAR ) * sum( Y.YEAR * AVG(D.AMOUNT) )) - (sum( AVG(D.AMOUNT) ) * sum(power(Y.YEAR, 2)))) / (power(sum(Y.YEAR), 2) - count(1) * sum(power(Y.YEAR, 2))) as INTERCEPT FROM CITY C, STATION S, YEAR_REF Y, MONTH_REF M, DAILY D WHERE $X{ IN, C.ID, CityCode } AND SQRT( POW( C.LATITUDE - S.LATITUDE, 2 ) + POW( C.LONGITUDE - S.LONGITUDE, 2 ) ) < $P{Radius} AND S.STATION_DISTRICT_ID = Y.STATION_DISTRICT_ID AND Y.YEAR BETWEEN 1900 AND 2009 AND M.YEAR_REF_ID = Y.ID AND M.CATEGORY_ID = $P{CategoryCode} AND M.ID = D.MONTH_REF_ID AND D.DAILY_FLAG_ID <> 'M' GROUP BY Y.YEAR </code></pre> <p>The things <em>will not work</em> straight from the query above (it has nonsensically combined aggregates and other errors); this can be a good time to check your formulas</p> <p>If you decide to do sub queries do simplify the formulas, then:</p> <ul> <li>you can grab (you do grab) all the necessary data in the inner most query and you don't have to repeat all the tables in the outer queries any more (just select the relevant columns from the t, they are already at your disposal)</li> <li>you don't have to repeat the where condition</li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload