每个类别的SQL计数

Question

我需要根据两个字段进行分组计算行数。

动物（a）

id     group_id   strain_id     death_date     death_cause   status
-----------------------------------------------------------------------
1      512        164           2015-12-01     Culled        P
2      512        164           2015-12-02     Culled        A
3      512        164           2015-12-02     Surplus       B
4      512        230           2015-12-06     Culled        A
5      512        164           2015-12-28     Culled        A
6      512        230           2016-01-20     Culled        B
7      512        230           2016-01-20     Surplus       P
8      512        164           NULL           NULL          P
9      512        230           NULL           NULL          B
10     512        230           NULL           NULL          A
11     512        164           2016-01-25     Culled        B
12     512        164           2016-02-29     Culled        A
13     512        230           2016-02-03     Surplus       P
14     512        230           2016-02-03     Culled        A

Groups (g)

id     group_name
--------------
512    Mice

应变 (s)

id     strain_name
----------------
164    Strain 1
230    Strain 2

小组动物数量 (gac)

id     total_animals      alive_count       dead_count
----------------------------------------------------------------------
512    14                 3                 11

交配历史 (mh)

id     animal_id     history_type      history_date
--------------------------------------------------------
1001   2             MA                2015-11-20
1002   2             MR                2015-12-01
1003   3             MA                2015-12-01
1004   6             FA                2015-12-21
1005   9             FA                2016-02-07
1006   10            MA                2016-01-27
1007   11            FA                2015-12-12

当我按照菌株 ID和死因进行分组时，它们的可视化结果如下：

菌株1 ---- 淘汰

1      512        164           2015-12-01     Culled        P
2      512        164           2015-12-02     Culled        A
5      512        164           2015-12-28     Culled        A
11     512        164           2016-01-25     Culled        B
12     512        164           2016-02-29     Culled        A

应变1 ---- 剩余

3      512        164           2015-12-02     Surplus       B

育种2——淘汰

4      512        230           2015-12-06     Culled        A
6      512        230           2016-01-20     Culled        B
14     512        230           2016-02-03     Culled        A

压力2 ---- 剩余

7      512        230           2016-01-20     Surplus       P
13     512        230           2016-02-03     Surplus       P

我希望从SQL查询中获得以下结果：

g_name  s_name    d_cause  a_total  c_alive  c_dead  c_pup  c_breeder  c_total
------------------------------------------------------------------------------
Mice    Strain 1  Culled   12       3        9       1      2          5
Mice    Strain 1  Surplus  12       3        9       0      1          1
Mice    Strain 2  Culled   12       3        9       0      1          3
Mice    Strain 2  Surplus  12       3        9       2      0          2

基本上，我想要按照两个分类（在这种情况下为strain_name和death_cause）计算动物数量。

请注意，要将动物计算为育种动物（c_breeder），我必须查看交配历史表，并检查animal_id是否曾经有过以下代码之一MA或FA。

我使用groups，group_animal_count和strains进行INNER JOIN。由于状态为P的动物只是幼仔，不参与交配活动，因此我对mating_history使用LEFT JOIN。

- dokgu

---

这是您正在使用的确切查询，还是还涉及一些LEFT JOIN？ - Giorgos Betsos

@GiorgosBetsos 这里有3个INNER JOIN和1个LEFT JOIN。 - dokgu

你必须给我们一些额外的信息。根据提供的表结构、示例数据和查询，无法重现你遇到的问题。 - Giorgos Betsos

@GiorgosBetsos 我更新了问题，我认为这个更接近我实际正在做的事情。 - dokgu

你不能只是按group_name、strain_name和death_cause分组（使用内连接来连接表），然后计算death_dates的数量吗？ - maraca

Answer 1

尝试：

SELECT group_name, strain_name,death_cause, count(*) as Total
FROM ANIMALS a
JOIN GROUPS g ON a.group_id = g.id
JOIN STRAIN s ON a.strain_id = s.id
GROUP BY group_name, strain_name,death_cause

- krokodilko

---

你的解决方案和@maraca的看起来给出了相同的结果。我明天需要验证一下。我即将离开办公室。感谢您的贡献！ - dokgu

@SaravanakumarVadivel 我已经尽可能详细地更新了问题。这个解决方案确实有效，但我的原始问题没有包括我在查询中进行的所有“连接”。一旦我添加了所有内容，数字就不匹配了。请再次查看。希望我的更新后的问题能够展示我想要实现的目标。 - dokgu

Answer 2

你可以在连接表之前进行聚合：

SELECT group_name, strain_name, death_cause, total
FROM   (
         SELECT   group_id,
                  strain_id,
                  death_cause,
                  COUNT(*) AS total
         FROM     animals
         GROUP BY group_id, strain_id, death_cause
       ) a
       INNER JOIN groups g ON ( g.group_id = a.group_id )
       INNER JOIN strain s ON ( s.strain_id = a.strain_id );

- MT0

---

感谢您的输入。我尝试了这个方法，似乎可以工作，但是当我添加了一个 LEFT JOIN 后，数字不再相加了。我已经尽可能详细地更新了问题，请再次查看。希望您能帮助我解决问题。 - dokgu