mysql - MYSQL 聚合函数计数不正确

Question

我有一个查询，我试图用它来根据用户的 ID 获取我们公司的销售额。它会提取他们的名字/姓氏、状态、下线代表以及最近输入的销售记录。然后应该提取所有销售的美元金额并将它们加在一起(注意:此列是后来添加的，作为销售部门的临时修复)。然后，对于每次销售，它应该将美元值(value)转换为基于产品的积分值(value)。它还应该找到销售的总提交点值和总关闭点值。我只需要每个销售人员 1 行。

这是查询的当前迭代:

SELECT preps_contracted_reps.first_name AS Rep_First_Name,
    preps_contracted_reps.last_name AS Rep_Last_Name,
    preps_contracted_reps_cstm.contact_status_reps_c AS Rep_Status,
    COUNT(wn_writing_number_cstm.id_c) AS Num_Reps_in_Downline,
    MAX(opportunities.date_entered) AS Date_Entered,
    aos_products.`name` AS Product_Name,
    SUM(opportunities_cstm.final_sale_amount_c) AS Total_in_Dollars,
    CASE 
        WHEN aos_products.NAME = 'LifeSure'
            THEN SUM(opportunities_cstm.final_sale_amount_c)
        WHEN aos_products.NAME LIKE '%SCF%'
            THEN SUM(opportunities_cstm.final_sale_amount_c * .125)
        WHEN aos_products.NAME LIKE '%(PAC)%'
            THEN SUM(opportunities_cstm.final_sale_amount_c * .833)
        WHEN aos_products.NAME LIKE '%Income Select%'
            AND aos_products.NAME NOT LIKE '%(PAC)%'
            THEN SUM(opportunities_cstm.final_sale_amount_c * .166)
        WHEN aos_products.NAME = 'Estate Enhancer'
            THEN SUM(opportunities_cstm.final_sale_amount_c * .333)
        WHEN aos_products.NAME LIKE '%Oil%'
            THEN SUM(opportunities_cstm.final_sale_amount_c * .33)
        WHEN aos_products.NAME LIKE '%Merchant Cash Advance%'
            THEN SUM(opportunities_cstm.final_sale_amount_c * .16)
        WHEN aos_products.NAME LIKE '%WB%'
            THEN SUM(opportunities_cstm.final_sale_amount_c * .025)
        WHEN aos_products.NAME LIKE '%Land Lease%'
            THEN SUM(opportunities_cstm.final_sale_amount_c * .625)
        WHEN aos_products.NAME LIKE '%Petro%'
            THEN SUM(opportunities_cstm.final_sale_amount_c * .625)
        ELSE opportunities_cstm.final_sale_amount_c
        END AS All_Time_Closed_Points,
    CASE 
        WHEN aos_products.NAME = 'LifeSure'
            THEN SUM(opportunities.amount)
        WHEN aos_products.NAME LIKE '%SCF%'
            THEN SUM(opportunities.amount * .125)
        WHEN aos_products.NAME LIKE '%(PAC)%'
            THEN SUM(opportunities.amount * .833)
        WHEN aos_products.NAME LIKE '%Income Select%'
            AND aos_products.NAME NOT LIKE '%(PAC)%'
            THEN SUM(opportunities.amount * .166)
        WHEN aos_products.NAME = 'Estate Enhancer'
            THEN SUM(opportunities.amount * .333)
        WHEN aos_products.NAME LIKE '%Oil%'
            THEN SUM(opportunities.amount * .33)
        WHEN aos_products.NAME LIKE '%Merchant Cash Advance%'
            THEN SUM(opportunities.amount * .16)
        WHEN aos_products.NAME LIKE '%WB%'
            THEN SUM(opportunities.amount * .025)
        WHEN aos_products.NAME LIKE '%Land Lease%'
            THEN SUM(opportunities.amount * .625)
        WHEN aos_products.NAME LIKE '%Petro%'
            THEN SUM(opportunities.amount * .625)
        ELSE opportunities.amount
        END AS All_Time_Submitted_Points
FROM wn_writing_number
RIGHT JOIN wn_writing_number_cstm
    ON wn_writing_number.id = wn_writing_number_cstm.id_c
INNER JOIN preps_contracted_reps_wn_writing_number_1_c
    ON wn_writing_number_cstm.id_c = preps_contracted_reps_wn_writing_number_1_c.preps_contracted_reps_wn_writing_number_1wn_writing_number_idb
INNER JOIN preps_contracted_reps
    ON preps_contracted_reps_wn_writing_number_1_c.preps_cont9effed_reps_ida = preps_contracted_reps.id
INNER JOIN preps_contracted_reps_cstm
    ON preps_contracted_reps.id = preps_contracted_reps_cstm.id_c
LEFT JOIN opportunities_cstm
    ON preps_contracted_reps.id = opportunities_cstm.preps_contracted_reps_id1_c
LEFT JOIN opportunities
    ON opportunities.id = opportunities_cstm.id_c
LEFT JOIN aos_products
    ON opportunities_cstm.aos_products_id_c = aos_products.id
LEFT JOIN aos_products_cstm
    ON aos_products.id = aos_products_cstm.id_c
WHERE preps_contracted_reps_cstm.preps_contracted_reps_id_c = "zcrm_1130062000004859021"
    AND preps_contracted_reps.deleted = "0"
    AND contact_status_reps_c <> "Terminated"
    AND contact_status_reps_c <> "Declined"
GROUP BY preps_contracted_reps.id
ORDER BY preps_contracted_reps_cstm.preps_contracted_reps_id_c ASC,
    Last_Name ASC,
    First_Name ASC,
    Date_Entered ASC

电流输出示例:

Rep_First_Name | Rep_Last_Name | Rep_Status | Num_Reps_in_Downline | Date_Entered | Product_Name | Total_in_Dollars | All_Time_Closed_Points | All_Time_Submitted_Points

          John |   Smith   |   Contracted   |        5         | 2017-12-05 14:6:41 | 12 Month Product | 393000.000000 | 245625.000000 | 233125 |
          Jane |   Doe     |  NeedsTraining |        0         | 2017-12-05 15:27:12| Null             |    Null       |    Null       |  Null  |

我遇到的问题是 Case 语句计算不正确。当我查看所有单独销售并进行计算时，我在 All_Time_Closed_Points 和 All_Time_Subscribed_Points 列中得到不同的数字。

我搜索了几个论坛，发现了一些关于分组依据问题的建议，但是当我尝试调整分组依据时，它通常只返回 1 行，其中所有信息都相加。另外，我尝试在没有聚合函数的情况下运行几种变体，但无济于事。我确实尝试只带回订单项，但我无法完全让它带回所有销售额。它只会带来 1。也许是因为 join 或 group by？我不知道下一步该去哪里。我很有可能没有搜索正确的关键字，或者我已经看到了这些信息，但不知道需要如何操作它。感谢所有帮助。

编辑:我需要发生两件事之一。我要么需要每个销售代表的所有销售额来计算他们的总销售额(当前是正确的)、提交的总积分和关闭的总积分(当前计算均不正确)，或者我需要返回每笔销售所有代表以及 aos_product.name。当我删除该组以提取各个代表的所有销售额时，它会汇总为一个代表，而不是 230 个代表，并将所有统计数据加在一起。所以在这方面，分组依据是错误的，但我不确定接下来应该添加什么。希望这有助于澄清。

编辑2:

SELECT 
    preps_contracted_reps.first_name AS Rep_First_Name,
    preps_contracted_reps.last_name AS Rep_Last_Name,
    preps_contracted_reps_cstm.contact_status_reps_c AS Rep_Status,
    COUNT(wn_writing_number_cstm.id_c) AS Num_Reps_in_Downline,
    MAX(opportunities.date_entered) AS Date_Entered,
    aos_products.`name` AS Product_Name,
    SUM(opportunities_cstm.final_sale_amount_c) AS Total_in_Dollars,
    opportunities_cstm.final_sale_amount_c,
    CASE 
        WHEN aos_products.NAME = 'LifeSure'
            THEN opportunities_cstm.final_sale_amount_c
        WHEN aos_products.NAME LIKE '%SCF%'
            THEN opportunities_cstm.final_sale_amount_c * .125
        WHEN aos_products.NAME LIKE '%(PAC)%'
            THEN opportunities_cstm.final_sale_amount_c * .833
        WHEN aos_products.NAME LIKE '%Income Select%' AND aos_products.NAME NOT LIKE '%(PAC)%'
            THEN opportunities_cstm.final_sale_amount_c * .166
        WHEN aos_products.NAME = 'Estate Enhancer'
            THEN opportunities_cstm.final_sale_amount_c * .333
        WHEN aos_products.NAME LIKE '%Oil%'
            THEN opportunities_cstm.final_sale_amount_c * .33
        WHEN aos_products.NAME LIKE '%Merchant Cash Advance%'
            THEN opportunities_cstm.final_sale_amount_c * .16
        WHEN aos_products.NAME LIKE '%WB%'
            THEN opportunities_cstm.final_sale_amount_c * .025
        WHEN aos_products.NAME LIKE '%Land Lease%'
            THEN opportunities_cstm.final_sale_amount_c * .625
        WHEN aos_products.NAME LIKE '%Petro%'
            THEN opportunities_cstm.final_sale_amount_c * .625
        ELSE opportunities_cstm.final_sale_amount_c
    END AS All_Time_Closed_Points,
    opportunities.amount,
    CASE 
        WHEN aos_products.NAME = 'LifeSure'
            THEN opportunities.amount
        WHEN aos_products.NAME LIKE '%SCF%'
            THEN opportunities.amount * .125
        WHEN aos_products.NAME LIKE '%(PAC)%'
            THEN opportunities.amount * .833
        WHEN aos_products.NAME LIKE '%Income Select%'
            AND aos_products.NAME NOT LIKE '%(PAC)%'
            THEN opportunities.amount * .166
        WHEN aos_products.NAME = 'Estate Enhancer'
            THEN opportunities.amount * .333
        WHEN aos_products.NAME LIKE '%Oil%'
            THEN opportunities.amount * .33
        WHEN aos_products.NAME LIKE '%Merchant Cash Advance%'
            THEN opportunities.amount * .16
        WHEN aos_products.NAME LIKE '%WB%'
            THEN opportunities.amount * .025
        WHEN aos_products.NAME LIKE '%Land Lease%'
            THEN opportunities.amount * .625
        WHEN aos_products.NAME LIKE '%Petro%'
            THEN opportunities.amount * .625
        ELSE opportunities.amount
    END AS All_Time_Submitted_Points
FROM wn_writing_number 
JOIN wn_writing_number_cstm ON wn_writing_number.id = wn_writing_number_cstm.id_c
JOIN preps_contracted_reps_wn_writing_number_1_c ON wn_writing_number_cstm.id_c = preps_contracted_reps_wn_writing_number_1wn_writing_number_idb
JOIN preps_contracted_reps ON preps_contracted_reps_wn_writing_number_1_c.preps_cont9effed_reps_ida = preps_contracted_reps.id
JOIN preps_contracted_reps_cstm ON preps_contracted_reps.id = preps_contracted_reps_cstm.id_c
LEFT JOIN opportunities_cstm ON preps_contracted_reps.id = opportunities_cstm.preps_contracted_reps_id1_c
LEFT JOIN opportunities ON opportunities.id = opportunities_cstm.id_c
LEFT JOIN aos_products ON opportunities_cstm.aos_products_id_c = aos_products.id
LEFT JOIN aos_products_cstm ON aos_products.id = aos_products_cstm.id_c
WHERE preps_contracted_reps_cstm.preps_contracted_reps_id_c = "zcrm_1130062000004859021" AND preps_contracted_reps.deleted = "0" AND contact_status_reps_c <> "Terminated" AND contact_status_reps_c <> "Declined"
GROUP BY preps_contracted_reps.id

编辑 3:以下是每个销售代表的多项销售，但不是全部。它还在每次销售的 Num_Reps_in_Downline 列中放入不同的数字或代表，这对我来说很有趣，因为我认为它只会在所有行中放入相同的总数。

SELECT pcr.first_name AS Rep_First_Name,
    pcr.last_name AS Rep_Last_Name,
    pcrc.contact_status_reps_c AS Rep_Status,
    COUNT(wnc.id_c) AS Num_Reps_in_Downline,
    MAX(o.date_entered) AS Date_Entered,
    ap.name AS Product_Name,
    SUM(oc.final_sale_amount_c) AS Total_in_Dollars,
    CASE 
        WHEN ap.NAME = 'LifeSure'
            THEN oc.final_sale_amount_c
        WHEN ap.NAME LIKE '%SCF%'
            THEN oc.final_sale_amount_c * .125
        WHEN ap.NAME LIKE '%(PAC)%'
            THEN oc.final_sale_amount_c * .833
        WHEN ap.NAME LIKE '%Income Select%' AND ap.NAME NOT LIKE '%(PAC)%'
            THEN oc.final_sale_amount_c * .166
        WHEN ap.NAME = 'Estate Enhancer'
            THEN oc.final_sale_amount_c * .333
        WHEN ap.NAME LIKE '%Oil%'
            THEN oc.final_sale_amount_c * .33
        WHEN ap.NAME LIKE '%Merchant Cash Advance%'
            THEN oc.final_sale_amount_c * .16
        WHEN ap.NAME LIKE '%WB%'
            THEN oc.final_sale_amount_c * .025
        WHEN ap.NAME LIKE '%Land Lease%'
            THEN oc.final_sale_amount_c * .625
        WHEN ap.NAME LIKE '%Petro%'
            THEN oc.final_sale_amount_c * .625
        ELSE oc.final_sale_amount_c
    END AS All_Time_Closed_Points,
    CASE 
        WHEN ap.NAME = 'LifeSure'
            THEN o.amount
        WHEN ap.NAME LIKE '%SCF%'
            THEN o.amount * .125
        WHEN ap.NAME LIKE '%(PAC)%'
            THEN o.amount * .833
        WHEN ap.NAME LIKE '%Income Select%'
            AND ap.NAME NOT LIKE '%(PAC)%'
            THEN o.amount * .166
        WHEN ap.NAME = 'Estate Enhancer'
            THEN o.amount * .333
        WHEN ap.NAME LIKE '%Oil%'
            THEN o.amount * .33
        WHEN ap.NAME LIKE '%Merchant Cash Advance%'
            THEN o.amount * .16
        WHEN ap.NAME LIKE '%WB%'
            THEN o.amount * .025
        WHEN ap.NAME LIKE '%Land Lease%'
            THEN o.amount * .625
        WHEN ap.NAME LIKE '%Petro%'
            THEN o.amount * .625
        ELSE o.amount
    END AS All_Time_Submitted_Points
FROM wn_writing_number wn
JOIN wn_writing_number_cstm wnc ON wn.id = wnc.id_c
JOIN preps_contracted_reps_wn_writing_number_1_c pcrwn ON wnc.id_c = pcrwn.preps_contracted_reps_wn_writing_number_1wn_writing_number_idb
JOIN preps_contracted_reps pcr ON pcrwn.preps_cont9effed_reps_ida = pcr.id
JOIN preps_contracted_reps_cstm pcrc ON pcr.id = pcrc.id_c
LEFT JOIN opportunities_cstm oc ON pcr.id = oc.preps_contracted_reps_id1_c
LEFT JOIN opportunities o ON o.id = oc.id_c
LEFT JOIN aos_products ap ON oc.aos_products_id_c = ap.id
LEFT JOIN aos_products_cstm apc ON ap.id = apc.id_c
WHERE pcrc.preps_contracted_reps_id_c = "503c4c33-d6cb-677d-0f6a-5a6f8b7aa1cc" AND pcr.deleted = "0" AND contact_status_reps_c <> "Terminated" AND contact_status_reps_c <> "Declined"
GROUP BY pcr.id, ap.name
ORDER BY pcr.last_name, pcr.first_name

Answer 1

查询的简化版本，没有任何GROUP BY。查看是否包含所需的记录。

SELECT pcr.first_name AS Rep_First_Name,
    pcr.last_name AS Rep_Last_Name,
    pcrc.contact_status_reps_c AS Rep_Status,
    --COUNT(wn_writing_number_cstm.id_c) AS Num_Reps_in_Downline,
    wn.id_c,
    --MAX(opportunities.date_entered) AS Date_Entered,
    o.date_entered,
    ap.name AS Product_Name,
    --SUM(opportunities_cstm.final_sale_amount_c) AS Total_in_Dollars,
    oc.final_sale_amount_c,
    CASE 
        WHEN ap.NAME = 'LifeSure'
            THEN oc.final_sale_amount_c
        WHEN ap.NAME LIKE '%SCF%'
            THEN oc.final_sale_amount_c * .125
        WHEN ap.NAME LIKE '%(PAC)%'
            THEN oc.final_sale_amount_c * .833
        WHEN ap.NAME LIKE '%Income Select%' AND ap.NAME NOT LIKE '%(PAC)%'
            THEN oc.final_sale_amount_c * .166
        WHEN ap.NAME = 'Estate Enhancer'
            THEN oc.final_sale_amount_c * .333
        WHEN ap.NAME LIKE '%Oil%'
            THEN oc.final_sale_amount_c * .33
        WHEN ap.NAME LIKE '%Merchant Cash Advance%'
            THEN oc.final_sale_amount_c * .16
        WHEN ap.NAME LIKE '%WB%'
            THEN oc.final_sale_amount_c * .025
        WHEN ap.NAME LIKE '%Land Lease%'
            THEN oc.final_sale_amount_c * .625
        WHEN ap.NAME LIKE '%Petro%'
            THEN oc.final_sale_amount_c * .625
        ELSE oc.final_sale_amount_c
    END AS All_Time_Closed_Points,
    o.amount,
    CASE 
        WHEN ap.NAME = 'LifeSure'
            THEN o.amount
        WHEN ap.NAME LIKE '%SCF%'
            THEN o.amount * .125
        WHEN ap.NAME LIKE '%(PAC)%'
            THEN o.amount * .833
        WHEN ap.NAME LIKE '%Income Select%'
            AND ap.NAME NOT LIKE '%(PAC)%'
            THEN o.amount * .166
        WHEN ap.NAME = 'Estate Enhancer'
            THEN o.amount * .333
        WHEN ap.NAME LIKE '%Oil%'
            THEN o.amount * .33
        WHEN ap.NAME LIKE '%Merchant Cash Advance%'
            THEN o.amount * .16
        WHEN ap.NAME LIKE '%WB%'
            THEN o.amount * .025
        WHEN ap.NAME LIKE '%Land Lease%'
            THEN o.amount * .625
        WHEN ap.NAME LIKE '%Petro%'
            THEN o.amount * .625
        ELSE o.amount
    END AS All_Time_Submitted_Points
FROM wn_writing_number wn
JOIN wn_writing_number_cstm wnc ON wn.id = wnc.id_c
JOIN preps_contracted_reps_wn_writing_number_1_c pcrwn ON wnc.id_c = pcrwn.preps_contracted_reps_wn_writing_number_1wn_writing_number_idb
JOIN preps_contracted_reps pcr ON pcrwn.preps_cont9effed_reps_ida = pcr.id
JOIN preps_contracted_reps_cstm pcrc ON pcr.id = pcrc.id_c
LEFT JOIN opportunities_cstm oc ON pcr.id = oc.preps_contracted_reps_id1_c
LEFT JOIN opportunities o ON o.id = oc.id_c
LEFT JOIN aos_products ap ON oc.aos_products_id_c = ap.id
LEFT JOIN aos_products_cstm apc ON ap.id = apc.id_c
WHERE pcrc.preps_contracted_reps_id_c = "zcrm_1130062000004859021" AND pcr.deleted = "0" AND contact_status_reps_c <> "Terminated" AND contact_status_reps_c <> "Declined"