**Code**

The following code calculates the slope and intercept for a linear regression against a slathering of data. It then applies the equation `y = mx + b` against the same result set to calculate the value of the regression line for each row.

How can the two queries be joined so that the data and its slope/intercept are calculated without executing the `WHERE` clause twice?

The general form of the problem is:

```
SELECT a.group, func(a.group, avg_avg)
FROM a
    (SELECT AVG(field1_avg) as avg_avg
     FROM (SELECT a.group, AVG(field1) as field1_avg
           FROM a
           WHERE (SOME_CONDITION)
           GROUP BY a.group) as several_lines -- potentially
    ) as one_line -- always
WHERE (SOME_CONDITION)
GROUP BY a.group -- again, potentially several lines

```

I have `SOME_CONDITION` executing twice. This is shown below (updated with a `STRAIGHT_JOIN` optimization):

```
SELECT STRAIGHT_JOIN
  AVG(D.AMOUNT) as AMOUNT,
  Y.YEAR * ymxb.SLOPE + ymxb.INTERCEPT as REGRESSION_LINE,
  Y.YEAR as YEAR,
  MAKEDATE(Y.YEAR,1) as AMOUNT_DATE,
  ymxb.SLOPE,
  ymxb.INTERCEPT,
  ymxb.CORRELATION,
  ymxb.MEASUREMENTS
FROM
  CITY C,
  STATION S,
  STATION_DISTRICT SD,
  YEAR_REF Y,
  MONTH_REF M,
  DAILY D,
  (SELECT
    SUM(MEASUREMENTS) as MEASUREMENTS,

    ((sum(t.YEAR) * sum(t.AMOUNT)) - (count(1) * sum(t.YEAR * t.AMOUNT))) /
    (power(sum(t.YEAR), 2) - count(1) * sum(power(t.YEAR, 2))) as SLOPE,

    ((sum( t.YEAR ) * sum( t.YEAR * t.AMOUNT )) -
    (sum( t.AMOUNT ) * sum(power(t.YEAR, 2)))) /
    (power(sum(t.YEAR), 2) - count(1) * sum(power(t.YEAR, 2))) as INTERCEPT,

    ((avg(t.AMOUNT * t.YEAR)) - avg(t.AMOUNT) * avg(t.YEAR)) /
    (stddev( t.AMOUNT ) * stddev( t.YEAR )) as CORRELATION
  FROM (
    SELECT STRAIGHT_JOIN
      COUNT(1) as MEASUREMENTS,
      AVG(D.AMOUNT) as AMOUNT,
      Y.YEAR as YEAR
    FROM
      CITY C,
      STATION S,
      STATION_DISTRICT SD,
      YEAR_REF Y,
      MONTH_REF M,
      DAILY D
    WHERE
      -- For a specific city ...
      --
      $X{ IN, C.ID, CityCode } AND

      -- Find all the stations within a specific unit radius ...
      --
      6371.009 *
      SQRT(
        POW(RADIANS(C.LATITUDE_DECIMAL - S.LATITUDE_DECIMAL), 2) +
        (COS(RADIANS(C.LATITUDE_DECIMAL + S.LATITUDE_DECIMAL) / 2) *
         POW(RADIANS(C.LONGITUDE_DECIMAL - S.LONGITUDE_DECIMAL), 2)) ) <= $P{Radius} AND

      SD.ID = S.STATION_DISTRICT_ID AND

      -- Gather all known years for that station ...
      --
      Y.STATION_DISTRICT_ID = SD.ID AND

      -- The data before 1900 is shaky; insufficient after 2009.
      --
      Y.YEAR BETWEEN 1900 AND 2009 AND

      -- Filtered by all known months ...
      --
      M.YEAR_REF_ID = Y.ID AND

      -- Whittled down by category ...
      --
      M.CATEGORY_ID = $P{CategoryCode} AND

      -- Into the valid daily climate data.
      --
      M.ID = D.MONTH_REF_ID AND
      D.DAILY_FLAG_ID <> 'M'
    GROUP BY
      Y.YEAR
  ) t
) ymxb
WHERE
  -- For a specific city ...
  --
  $X{ IN, C.ID, CityCode } AND

  -- Find all the stations within a specific unit radius ...
  --
  6371.009 *
  SQRT(
    POW(RADIANS(C.LATITUDE_DECIMAL - S.LATITUDE_DECIMAL), 2) +
    (COS(RADIANS(C.LATITUDE_DECIMAL + S.LATITUDE_DECIMAL) / 2) *
     POW(RADIANS(C.LONGITUDE_DECIMAL - S.LONGITUDE_DECIMAL), 2)) ) <= $P{Radius} AND

  SD.ID = S.STATION_DISTRICT_ID AND

  -- Gather all known years for that station ...
  --
  Y.STATION_DISTRICT_ID = SD.ID AND

  -- The data before 1900 is shaky; insufficient after 2009.
  --
  Y.YEAR BETWEEN 1900 AND 2009 AND

  -- Filtered by all known months ...
  --
  M.YEAR_REF_ID = Y.ID AND

  -- Whittled down by category ...
  --
  M.CATEGORY_ID = $P{CategoryCode} AND

  -- Into the valid daily climate data.
  --
  M.ID = D.MONTH_REF_ID AND
  D.DAILY_FLAG_ID <> 'M'
GROUP BY
  Y.YEAR

```

**Question**

How do I execute the duplicate bits only once per query, instead of twice? The duplicate code:

```
  $X{ IN, C.ID, CityCode } AND
  6371.009 *
  SQRT(
    POW(RADIANS(C.LATITUDE_DECIMAL - S.LATITUDE_DECIMAL), 2) +
    (COS(RADIANS(C.LATITUDE_DECIMAL + S.LATITUDE_DECIMAL) / 2) *
     POW(RADIANS(C.LONGITUDE_DECIMAL - S.LONGITUDE_DECIMAL), 2)) ) <= $P{Radius} AND
  SD.ID = S.STATION_DISTRICT_ID AND
  Y.STATION_DISTRICT_ID = SD.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

```

**Update 1**

Using variables and splitting the query seems to allow the cache to kick in as this now runs in 3.5 seconds, whereas it used to run in 7. Still, if there is any way to remove the duplicate code, I'd be grateful for any help.

**Update 2**

The above code does not run in JasperReports, and a VIEW, while a possible fix, would probably be extremely inefficient (because the WHERE clauses are parameterized).

**Update 3**

Validating distance using Unreason's suggestion of the Pythagorean formula with converging meridians:

```
  6371.009 *
  SQRT(
    POW(RADIANS(C.LATITUDE_DECIMAL - S.LATITUDE_DECIMAL), 2) +
    (COS(RADIANS(C.LATITUDE_DECIMAL + S.LATITUDE_DECIMAL) / 2) *
    POW(RADIANS(C.LONGITUDE_DECIMAL - S.LONGITUDE_DECIMAL), 2)) )

```

(This is unrelated to the question, but should someone else want to know ...)

**Update 4**

The code, as shown, works in JasperReports, running against a MySQL database. JasperReports does not allow variables or multiple queries.

**Update 5**

Am looking for a solution that executes cleanly. ;-) I have written a number of partially working solutions, but MySQL, sadly, does not understand *partially correct*. See the discussions with Unreason for answers that almost work.

**Update 6**

I might be able to reuse variables from the first `WHERE` clause and compare them to the second (thereby eliminating *some* duplication -- the checks against `$P{}` values), but I'd really like the duplication eliminated.

**Update 7**

Comparing the `YEAR` clause, as hypothesized in the previous update, to eliminate the duplicate `BETWEEN`, does not work.

**Related**

[How to eliminate duplicate calculation in SQL?](https://stackoverflow.com/questions/1595659/how-to-eliminate-duplicate-calculation-in-sql)

Thank you!