访问联盟和数数 [英] Access union & count

问题描述

我是一个初学者.我有这段SQL代码:

I'm a total beginner. I have this piece of SQL code:

SELECT Red_tag.Zglaszajacy
From Red_tag
UNION
SELECT Red_tag.Interweniujacy
From Red_tag

SELECT Count(Red_tag.Zglaszajacy) AS PoliczOfZglaszajacy, Red_tag.Interweniujacy AS PoliczOfInterweniujacy
FROM Red_tag

联盟有效，但是出现"FROM的语法错误".我如何获得这样的输出?

Union works, but I get "Syntax error of FROM". How do I get the output like this?

|Zglaszajacy&Interweniujacy|CountOfZglaszajacy|CountOfInterweniujący|

编辑 似乎它比我想象的要复杂. Zglaszajacy和Interweniujacy是一群人.这两个组也重叠(两个列中都有大约30％的人有记录).因此，在某些情况下，同一个人在Zglaszajacy中有3条记录，在Interweniujacy中有7条记录.这就是为什么我需要将这两个组合并，然后为每个人显示两个累计计数.我不确定我是否解释得很好，所以这是草稿:

EDIT Seems like its more complex than I thought. Zglaszajacy and Interweniujacy are sets of people. These two groups also overlap (about 30% of people have records in both columns). So there are cases when the same person has 3 records in Zglaszajacy and 7 entries in Interweniujacy. That is why I need to Union this two groups and then show two cumulated counts for each man. Im not sure if I explained it well, so here's a draft:

|Union of Zglaszający and Interweniujacy |Count of Zglaszajacy| Count of Interweniujacy|
---------------------------------------------------------------------------------------
|John Doe                               |     3              |  5                     |
|Tom Smith                              |  NULL              |  1                     |

推荐答案

通过使用UNION查询，您将获得一个看起来像这样的表:

By using your UNION query, you are taking a table that looks like this:

Zglaszajacy|Interweniujacy
A           B
C           NULL
NULL        B

并将其变成这样:

Zglaszajacy
A
C
NULL
B

您丢失了一些记录，因为UNION仅保留不同的记录.请改用UNION ALL保留所有记录.

You have lost some of your records because UNION only keeps distinct records. Use UNION ALL instead to retain all records.

您的第二个SQL查询无法正常工作，因为您没有GROUP BY子句来告诉聚合函数COUNT()它应该计数的内容.

Your second SQL query was not working because you did not have the GROUP BY clause to tell the aggregation function COUNT() what it was supposed to be counting.

我在下面所做的事情是使用您的UNION查询并添加一个标志来确定记录是Zglaszajacy类型还是Interweniujacy类型.然后，我将其用作FROM子句中的子查询.从那里，选择您的人名，然后使用COUNT()聚合函数(或者SUM()在这种情况下也可以使用，因为我用数字标记了它)来获取总计.

What I have done below is taken your UNION query and added a flag for if the record was the Zglaszajacy type or the Interweniujacy type. Then, I used that as a subquery in the FROM clause. From there, you select your person name and use the COUNT() aggregation function (or SUM() would work in this case too because I flagged it with a number) to get your totals.

最后，我添加了WHERE子句，以消除分组后剩下的单个NULL名称，我认为这对您没有用.

Last, I added the WHERE clause to eliminate your single NULL name that is left after grouping which I am assuming would not be useful to you.

SELECT Person_Name, COUNT(Z_Type) AS Zglaszajacy_Count, 
    COUNT(I_Type) AS Interweniujacy_Count
FROM (SELECT Zglaszajacy as Person_Name, 1 AS Z_Type, NULL AS I_Type FROM Red_tag
      UNION ALL
      SELECT Interweniujacy, NULL, 1 FROM Red_tag)
WHERE Person_Name IS NOT NULL
GROUP BY Person_Name
ORDER BY Person_Name;

我在MS Access中对此进行了测试，并且可以正常工作.

I tested this in MS Access and it works.

这篇关于访问联盟和数数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！