MySQL - getting SUM of MAX results from 2 tables

Question

Hi, Here's my problem: I have 2 identical tables (past month data, current month data) - data_2010_03, data_2010_04:

Content_type (VARCHAR), content_id (INT), month_count (INT), pubDate (DATETIME)

Data in month_count is updated hourly, so for each combination of content_type and content_id we insert new row, where value of month_count is incrementally updated.

Now I try something like this:
SELECT MAX(t1.month_count) AS max_1, MAX(t2.month_count) AS max_2, SUM(max_1 + max_2) AS result, t1.content_type, t1.content_id
FROM data_2010_03 AS t1
JOIN data_2010_04 AS t2 ON t1.content_type = t2.content_type AND t1.content_id = t2.content_id
WHERE t2.pubDate < '2010-04-08' AND t1.content_type = 'video'
GROUP BY t1.content_id
ORDER BY result desc, max_1 desc, max_2 desc
LIMIT 0,10

I get an error "Unknown column 'max_1' in 'field list'. Please help.

Answer 1

You can not use aliases in expressions (on the same level of select) so

SUM(max_1 + max_2) AS result

has to be written as

MAX(t1.month_count)+MAX(t2.month_count) AS result

Answer 2

Instead of

SELECT MAX(t1.month_count) AS max_1, MAX(t2.month_count) AS max_2, SUM(max_1 + max_2) AS result

Can you just do

SELECT MAX(t1.month_count) + MAX(t2.month_count) AS result

Or I may have misunderstood the requirement.