每个类别的 SQL 计数

Question

我需要根据 2 个字段对行进行计数以进行分组。

动物(一)

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

组(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

因此，当我按 strain_id 和 death_cause 将它们分组时，这就是它们在视觉上应该看起来的样子:

菌株 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

基本上我想使用 2 个类别来计算动物的数量，在本例中是 strain_name 和 death_cause

请注意，要将动物算作繁殖者 (c_breeder)，我必须查看交配历史表并检查 animal_id 是否曾经有过交配历史这些代码 MA 或 FA。

我在 groups、group_animal_count 和 strains 上使用 INNER JOIN。我将 LEFT JOIN 用于 mating_history，因为状态为 P 的动物不会在该表中有记录，因为它们只是小狗并且赢了'参与交配。

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