MySQL SELECT with LEFT JOIN and GROUP problem

Question

I have a main table foo with dates and values, and a table bar with exception dates and values. If the date exists in the bar table, that value applies, otherwise the default foo value. So, for example:

foo
id  date        value
1   2009-11-19  25
2   2009-11-20  50

bar
id  date        value
1   2009-11-19  50

To select the right values I use the following type of query:

SELECT
  f.date,
  IF (f.date = b.date, b.value, f.value) AS value
FROM foo f
LEFT JOIN bar b
  ON b.date = f.date

This works fine. The date "2009-11-19" exists in bar, so it selects the 50-value. But now I'd like to GROUP the results by MONTH, using this query:

SELECT
  MONTH(f.date) AS month,
  IF (f.date = b.date, b.value, f.value) AS value
FROM foo f
LEFT JOIN bar b
  ON b.date = f.date
GROUP BY month

It now returns the results by month, so the grouping works fine. However, it now ignores the bar values, and it seems it doesn't do any matching at all. The total for the month is 75 (25 from foo + 50 from foo) instead of 100 (50 from bar + 50 from foo).

Is there a way to make this work? So that it selects the correct values if there's a match between both tables, and after that grouping it by month? I can use the first query and do the grouping in PHP afterwards, but I'd like to get the right results straight from MySQL if that's possible.

Answer 1

Try using a subquery, like so:

SELECT MONTH(date), value FROM 
    (SELECT
      f.date,
      IF (f.date = b.date, b.value, f.value) AS value
    FROM foo f
    LEFT JOIN bar b ON b.date = f.date)
GROUP BY month

Answer 2

do the SELECT in a subquery and GROUP in the main query:

SELECT themonth, SUM(myvalue) FROM (
    SELECT
      MONTH(f.date) AS themonth,
      IF (f.date = b.date, b.value, f.value) AS myvalue
    FROM foo f
    LEFT JOIN bar b
      ON b.date = f.date) tmptable
GROUP BY themonth;