根据时间变量从两个表中求和一对COUNT [英] SUM a pair of COUNTs from two tables based on a time variable

问题描述

一个小时的大部分时间里都在寻找答案的过程中，运气不佳.我有两个具有相同列名的区域表，并且可以根据以下查询(为Table1交换Table2)为任一表列出结果列表:

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    |

我要:

|     MERGED       |
|  YEAR  |  Total  |
|  2010  |    6    |
|  2009  |    5    |
|  2008  |    4    |

我尝试了各种JOIN和其他构想，但我认为我对SUM和COUNT问题有所了解.任何帮助将不胜感激，谢谢！

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!

推荐答案

SELECT `YEAR`, FORMAT(SUM(`count`), 0) AS `Total`
FROM (
    SELECT `Table1`.`YEAR`, COUNT(*) AS `count`
    WHERE `Table1`.`variable` = 'Y'
    GROUP BY `Table1`.`YEAR`
    UNION ALL
    SELECT `Table2`.`YEAR`, COUNT(*) AS `count`
    WHERE `Table2`.`variable` = 'Y'
    GROUP BY `Table2`.`YEAR`
) AS `union`
GROUP BY `YEAR`

这篇关于根据时间变量从两个表中求和一对COUNT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！