Joining two subqueries in MySQL

Question

I've got two MySQL subqueries that on their own work fine, but when I join them together, the second column gives incorrect values.

The two queries in question simply show the total number of times a horse has raced, prior to the current race, and the sum of the times the horse has won prior to today.

I've tried each type of join and joining with different ids, however I just can't find the correct combination.

The output is effectively showing the race by race results.

Query A - total races

SELECT
  date,
  raceref,
  horse,
  win,

  (
    SELECT ifnull(count(raceref),0) as totalraces
    FROM results
    WHERE horse = t.horse
      AND date < t.date
    ORDER BY date DESC

  ) AS totalraces_prior

FROM results t
group by horse, raceref
order by raceref asc;

Query B - sum of wins

SELECT
  date,
  raceref,
  horse,
  win,

  (
    SELECT ifnull(sum(win),0) as wins
    FROM results
    WHERE horse = t.horse
      AND date < t.date
    ORDER BY date DESC

  ) AS totalwins_prior

FROM results t
group by horse, raceref
order by raceref;

I would love a fresh set of eyes.

Here is an SQL Fiddle showing my join.

http://www.sqlfiddle.com/#!2/87c54da/1

Just to examine the issue more closely, it's the 'totalwins' column that keeps giving incorrect values when I join the two queries. Or really it's whatever column I add second comes up with incorrect values.

The database is large and SQL Fiddle can't handle anything too large so here's an image of what occurs as it's not immediately obvious in SQL Fiddle.

http://tinypic.com/r/o0x7i8/8

Thanks guys.

Answer 1

select date, raceref, horse, win, totalraces, totalwins, (totalwins / totalraces) as winPercentage
from (
select date, raceref, horse, win, 
  (
    SELECT ifnull(count(raceref),0)
    FROM results
    WHERE horse = t.horse
      AND date < t.date
    ORDER BY date DESC

  ) AS totalraces,
   (
    SELECT ifnull(sum(win),0)
    FROM results
    WHERE horse = t.horse
      AND date < t.date
    ORDER BY date DESC

  ) AS totalwins

FROM results t
group by horse, raceref
order by raceref asc
) t
;