Help with sum of 2 tables

[cameraguy]

Hello,

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!

[coopster]

Like this?
How to add rows in multiple tables together [webmasterworld.com]

[cameraguy]

Yes coopster! Exactly.

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?

[aspdaddy]

Sounds like the subquery needs an alias, Have you tried:
FROM NC_P_stats_10000 as CountryAll

[cameraguy]

Thanks aspdaddy, tried it and still doesn't work.

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!

[syber]

I think you have your parens in the wrong place. It should be:

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