使用count函数联合多个查询

I'm working on learning more about how the UNION function works in SQL Server.

我正在研究如何在SQL Server中使用UNION函数。

I've got a query that is directed at a single table:

我有一个查询指向一个表:

SELECT Category, COUNT(*) AS Number
FROM Table1
GROUP BY Category;

This returns the number of entries for each distinct line in the Category column.

这将返回类别列中每一行的条目数。

I have multiple tables that are organized by this Category column and I'd like to be able to have the results for every table returned by one query.

我有多个由这个类别列组织的表，我希望能够对一个查询返回的每个表都有结果。

It seems like UNION will accomplish what I want it to do but the way I've tried implementing the query doesn't work with COUNT(*).

似乎UNION将完成我希望它做的事情，但是我尝试实现查询的方式与COUNT(*)不兼容。

SELECT *
FROM   (SELECT Table1.Category
               Table1.COUNT(*) AS Number
        FROM   dbo.Table1
        UNION
        SELECT Table2.Category
               Table2.COUNT(*) AS Number
        FROM   dbo.Table2) AS a
GROUP  BY a.Category

I'm sure there's an obvious reason why this doesn't work but can anyone point out what that is and how I could accomplish what I'm trying to do?

我肯定有一个很明显的原因，为什么这不起作用，但是谁能指出这是什么，以及我该如何完成我想要做的事情?

2 个解决方案

#1

You cannot write a common Group by clause for two different select's. You need to use Group by clause for each select

您不能为两个不同的选择编写一个通用的Group by子句。每个select都需要使用Group by子句。

SELECT TABLE1.Category, --missing comma here
       COUNT(*) as Number -- Remove TABLE1. alias name
FROM dbo.TABLE1
GROUP BY Category
UNION ALL --UNION
SELECT TABLE2.Category, --missing comma here
       COUNT(*) as Number -- Remove TABLE1. alias name
FROM dbo.TABLE2
GROUP BY Category

If you really want to remove duplicates in result then change UNION ALL to UNION

如果您真的想删除重复的结果，那么将UNION全部更改为UNION

#2

COUNT as any associated aggregation function has to have GROUP BY specified. You have to use group by for each sub query separately:

计数，因为任何相关的聚合函数都必须具有按指定的组。您必须对每个子查询分别使用group by:

SELECT * FROM (
    SELECT TABLE1.Category,
           COUNT(*) as Number
    FROM dbo.TABLE1
    GROUP BY TABLE1.Category
    UNION ALL
    SELECT TABLE2.Category,
           COUNT(*) as Number
    FROM dbo.TABLE2
    GROUP BY TABLE2.Category
) as a

It is better to use UNION ALL vs UNION - UNION eliminates duplicates from result sets, since - let say - you want to merge both results as they are it is safer to use UNION ALL

最好是使用UNION ALL vs UNION - UNION消除结果集的重复，因为——假设——您希望合并两个结果，因为它们是更安全的使用UNION ALL。

#1