按表分组返回MySQL中的不正确计数和左联接

Question

我试图从多个表中返回多个计数和平均数，并按性别排序，并得到不正确的数据。我知道以下是不正确的，但我不知道如何解决它。(编辑:按性别分类的问题。见下文。)

以下是查询：

SELECT c.gender AS 'Gender',
       COUNT(DISTINCT mr.mailing_recipient_id) AS 'Mailing Recipients',

  (SELECT COUNT(DISTINCT CASE WHEN mrc.mailing_recipient_click_type_id = 2 THEN 1 ELSE 0 END) ) AS 'Open Total',
       AVG(CASE WHEN mrc.mailing_recipient_click_type_id = 2 THEN 1 ELSE 0 END) AS 'Avg Open',

  (SELECT COUNT(DISTINCT CASE WHEN mrc.mailing_recipient_click_type_id = 1 THEN 1 ELSE 0 END) ) AS 'Click Total',
       AVG(CASE WHEN mrc.mailing_recipient_click_type_id = 1 THEN 1 ELSE 0 END) AS 'Avg Click',
       COUNT(DISTINCT ca.cons_action_contribution_id) AS Donations,
       AVG(ca.transaction_amt) AS 'Avg Donation Amt'
FROM ((mailing m
       LEFT JOIN mailing_recipient mr ON m.mailing_id = mr.mailing_id)
      LEFT JOIN mailing_recipient_click mrc ON mr.mailing_recipient_id = mrc.mailing_recipient_id
      LEFT JOIN cons_action_contribution ca ON mr.cons_id = ca.cons_id
      LEFT JOIN cons c ON c.cons_id = ca.cons_id)
WHERE m.mailing_id = 1
  AND gender IS NOT NULL
GROUP BY c.gender;

如果字段中的总数是正确的，下面的表将是正确的：

GENDER    Mailing Recipient    Open Total    Avg Open    Click Total    Avg Click    Donations     Avg Amt
F         105                  2             0.5000      2              0.5000       105           22.5000
M         98                   2             0.5000      2              0.5000       98            18.8780

编辑:这是我希望达到的目标的一个例子。我确信，上述价值观正在重复。下面的值只是我所期望的例子：

GENDER    Mailing Recipient    Open Total    Avg Open    Click Total    Avg Click    Donations     Avg Amt
F         105                  8             0.0761      4              0.0380       2             22.5000
M         98                   2             0.0204      1              0.0102       1             18.8000

编辑:在玩了一会儿之后，我想我已经发现了连接cons表是给我带来问题的回报，但是问题是在使用性别时使用组。为了说明这一点，这个查询(按邮件名称而不是性别分组)工作得很好。

select m.mailing_name AS 'mailing',
COUNT(DISTINCT mr.mailing_recipient_id) AS 'Mailing Recipients',
SUM(CASE
                when mrc.mailing_recipient_click_type_id = 2 THEN 1
            END)
     AS 'Open Total',
AVG(CASE
                WHEN mrc.mailing_recipient_click_type_id = 2 THEN 1
                ELSE 0
            END) AS 'Avg Open',
SUM(CASE
                WHEN mrc.mailing_recipient_click_type_id = 1 THEN 1
            END)
     AS 'Click Total',
 AVG(CASE
                WHEN mrc.mailing_recipient_click_type_id = 1 THEN 1
                ELSE 0
            END) AS 'Avg Click',
 COUNT(ca.cons_action_contribution_id) AS Donations,
 AVG(ca.transaction_amt) AS 'Avg Donation Amt'
 FROM
 mailing m 
 LEFT JOIN mailing_recipient mr ON m.mailing_id = mr.mailing_id
 LEFT JOIN mailing_recipient_click mrc 
ON mr.mailing_recipient_id = mrc.mailing_recipient_id   
LEFT JOIN cons_action_contribution ca ON mr.cons_id = ca.cons_id
LEFT JOIN cons c ON mr.cons_id = c.cons_id
WHERE  m.mailing_id = 1
GROUP BY m.mailing_name;

除了第一行和最后一行外，语句是相同的。

Answer 1

试试这个：

我不知道你说的Avg Open和Avg Click是什么意思。

SELECT c.gender AS 'Gender',
       COUNT(DISTINCT mr.mailing_recipient_id) AS 'Mailing Recipients',

       SUM(CASE WHEN mrc.mailing_recipient_click_type_id = 2 THEN 1 ELSE 0 END) AS 'Open Total',
       AVG(CASE WHEN mrc.mailing_recipient_click_type_id = 2 THEN 1 ELSE 0 END) AS 'Avg Open',

       SUM(CASE WHEN mrc.mailing_recipient_click_type_id = 1 THEN 1 ELSE 0 END)  AS 'Click Total',
       AVG(CASE WHEN mrc.mailing_recipient_click_type_id = 1 THEN 1 ELSE 0 END) AS 'Avg Click',
       COUNT(DISTINCT ca.cons_action_contribution_id) AS Donations,
       AVG(ca.transaction_amt) AS 'Avg Donation Amt'
FROM  mailing m
      LEFT JOIN mailing_recipient mr ON m.mailing_id = mr.mailing_id
      LEFT JOIN mailing_recipient_click mrc ON mr.mailing_recipient_id = mrc.mailing_recipient_id
      LEFT JOIN cons_action_contribution ca ON mr.cons_id = ca.cons_id
      LEFT JOIN cons c ON c.cons_id = ca.cons_id
WHERE m.mailing_id = 1
  AND gender IS NOT NULL
GROUP BY c.gender;

我还认为，mrc.mailing_recipient_click_type_id = 2意味着打开，而mrc.mailing_recipient_click_type_id = 1则意味着单击，对我来说似乎很奇怪。我希望这些数据是排他性的，并存储在两个不同的字段中。