SUM a pair of COUNTs from two tables based on a time variable
- by Kevin O.
Been searching for an answer to this for the better part of an hour without much luck. I have two regional tables laid out with the same column names and I can put out a result list for either table based on the following query (swap Table2 for Table1):
SELECT Table1.YEAR, FORMAT(COUNT(Table1.id),0) AS Total
FROM Table1
WHERE Table1.variable='Y'
GROUP BY Table1.YEAR
Ideally I'd like to get a result that gives me a total sum of the counts by year, so instead of:
| REGION 1 | | REGION 2 |
| YEAR | Total | | YEAR | Total |
| 2010 | 5 | | 2010 | 1 |
| 2009 | 2 | | 2009 | 3 |
| | | | 2008 | 4 |
I'd have:
| MERGED |
| YEAR | Total |
| 2010 | 6 |
| 2009 | 5 |
| 2008 | 4 |
I've tried a variety of JOINs and other ideas but I think I'm caught up on the SUM and COUNT issue. Any help would be appreciated, thanks!
