Forum Moderators: open
I am trying to add the values of 2 columns from 2 tables:
t1
--
country visitors
US 124
CA 87
MX 54
BR 15
t2
--
country visitors
US 98
CA 36
MX 15
The result I would like to get with a unique query is:
country visitors
US 222
CA 123
MX 69
BR 15
The query I am working with is:
SELECT (t1.visitors + t2.visitors )AS visitors, t1.country FROM t1, t2 GROUP BY t1.country ORDER BY visitors DESC
The problem is that for t2.visits I seem to be getting always the first value of the table t2 (98):
country visitors
US 222
CA 185
MX 152
BR 113
Can someone help please? Thank you!
I tried the following query:
SELECT country_id, SUM( visits ) AS totalvisits
FROM (
SELECT country_id, visits
FROM NC_P_stats_10000
)
UNION ALL (
SELECT country_id, visits
FROM NC_P_stats_10000_2
) AS countryAll
GROUP BY country_id
But I am getting the error: "Every derived table must have its own alias". FYI, I tested the the "fruit" example with exactly the same data and query and I am getting also this error message. (I am working with MySQL version 5.0.21).
What am I doing wrong?
But in the meantime I managed to write a query that works:
SELECT yesterday.country_id, (ifnull(yesterday.visits,0) + ifnull(COUNT(today.ip_address),0)) AS visits
FROM yesterday
LEFT JOIN today ON yesterday.country_id= today.country_id
GROUP BY yesterday.tool_id, yesterday.country_id
HAVING yesterday.tool_id = 12345 ORDER BY 'visits' DESC
My only problem now is that this query is VERY slow. Querying the 2 tables (yesterday and today) separately goes very fast, but the joining of both is, as I said, painfully slow.
I am now wondering whether I should rather use an array to combine both queries...
Anyone any toughts? Thanks!
SELECT country_id, SUM( visits ) AS totalvisits
FROM
( SELECT country_id, visits
FROM NC_P_stats_10000
UNION ALL
SELECT country_id, visits
FROM NC_P_stats_10000_2
) AS countryAll
GROUP BY country_id
