从sql中的2个不同表中求和两个计数

I have two tables on db:

我在db上有两个表：

1. memberOne

memberName | gender
===================
Jack       | M
Steve      | M
Audrey     | F


2. memberTwo

memberName | gender
===================
Sarah      | F
Steve      | M
Audrey     | F
Alvin      | M

I want to display this view:

我想显示这个视图：

Gender         | Total
=======================
M              | 4
F              | 3

I performed this code

我执行了这段代码

SELECT t.Gender, COUNT(t.Gender) Total FROM memberOne t
GROUP BY t.Gender
UNION ALL
SELECT d.Gender, COUNT(d.Gender) Total FROM memberTwo d
GROUP BY d.Gender
;

And this is what I got:

这就是我得到的：

Gender       |  Total
------------- ----------
M                      2 
F                      1 
M                      2 
F                      2 

How can I sum the total of M and F from each table? Should I use condition to check the gender? Any helps would be appreciated, thanks.

如何从每张表中总结M和F的总和？我应该使用条件检查性别吗？任何帮助将不胜感激，谢谢。

5 个解决方案

#1

You need to use UNION ALL and then apply COUNT

您需要使用UNION ALL然后应用COUNT

SELECT
    gender as Gender,
    COUNT(*) as Total
FROM
(
    SELECT gender
    FROM memberOne
    UNION ALL
    SELECT gender
    FROM memberTwo
) group by gender

#2

One approach here would be to union together only the genders from the two tables, and then do a single aggregation to get the male and female counts.

这里的一种方法是将两个表中的性别联合在一起，然后进行单个聚合以获得男性和女性计数。

SELECT
    gender,
    COUNT(*) AS total
FROM
(
    SELECT gender
    FROM memberOne
    UNION ALL
    SELECT gender
    FROM memberTwo
) t
GROUP BY gender
ORDER BY gender DESC

Demo here:

在这里演示：

Rextester

#3

Wrap your last query in another query that sums the count of M and F.

将您的上一个查询换行到另一个查询中，该查询将M和F的数量相加。

SELECT
    G, SUM(Total) 
FROM
    (SELECT  
         t.Gender G, COUNT(t.Gender) Total 
     FROM
         memberOne t
     GROUP BY 
         t.Gender

    UNION ALL

    SELECT 
        d.Gender G, COUNT(d.Gender) Total 
    FROM 
        memberTwo d
    GROUP BY 
        d.Gender) 
GROUP BY 
    gender

#4

One way to accomplish your goal working off your initial query would be:

完成初始查询的目标的一种方法是：

select Gender, sum(Total) Total From (your existing query) q group by q.Gender

从q.Gender中选择性别，总和（总计）来自（您现有的查询）q组

A different way would be:

另一种方式是：

select Gender, Count(Gender) Total from ( select Gender from membeOne Union all Select Gender from memberTwo ) q group by q.Gender

选择性别，计数（性别）总计来自（选择性别来自membeOne Union所有选择性别来自memberTwo）q group by q.Gender

#5

SELECT n.Gender, COUNT(n.Gender) Total FROM
(
SELECT t.Gender, COUNT(t.Gender) Total FROM memberOne t
GROUP BY t.Gender
UNION ALL
SELECT d.Gender, COUNT(d.Gender) Total FROM memberTwo d
GROUP BY d.Gender
) n
GROUP BY n.Gender

reiterating select and group by after last query

在最后一次查询后重复select和group by