即使在分组后，我也会获得重复值 [英] I am getting duplicate values even after group by

问题描述

SELECT      DISTINCT    --EU.ecpuser_ID
                                0 AS Total_Flag
                                ,CASE WHEN EU.ecpuser_type='p' THEN   EU.ecpuser_ID
                                    ELSE EU.ecpuser_fullname END AS USER_NAME
                                ,COALESCE((CASE WHEN ELT.ecpelginquirytransaction_current_status='Sent' THEN COUNT(ELT.ecpelginquirytransaction_current_status) END ),0) AS NOT_RESPONDED_COUNT
                                ,COALESCE((CASE WHEN ELT.ecpelginquirytransaction_current_status='Failed' THEN COUNT(ELT.ecpelginquirytransaction_current_status) END ),0) AS FAILED_COUNT
                                ,COALESCE((CASE WHEN ELT.ecpelginquirytransaction_current_status='Responded' THEN COUNT(ELT.ecpelginquirytransaction_current_status) END ),0)AS RESPONDED_COUNT
                                ,COALESCE((CASE WHEN ELT.ecpelginquirytransaction_current_status='Responded' THEN COUNT(ELT.ecpelginquirytransaction_current_status) END ),0)+ COALESCE((CASE WHEN ELT.ecpelginquirytransaction_current_status='Failed' THEN COUNT(ELT.ecpelginquirytransaction_current_status) END ),0)+ COALESCE((CASE WHEN ELT.ecpelginquirytransaction_current_status='Sent' THEN COUNT(ELT.ecpelginquirytransaction_current_status) END ),0) AS TOTAL_COUNT
                         FROM tbl_eCPElgInquiryTransaction ELT
                         INNER JOIN tbl_ecpuser EU ON (ELT.ecpelginquirytransaction_createdby=EU.ecpuser_key)
                         WHERE ecpaccount_key ='0481843194'  AND ELT.eCPElgInquiryTransaction_Sent_Date
                     BETWEEN '2012-11-27 22:00:01' AND '2013-12-04 22:00:00' AND EU.ecpuser_fullname='Lucindia Evans'  GROUP BY
                    EU.ecpuser_type
                   ,EU.ecpuser_ID
                   ,EU.ecpuser_fullname
                   ,ELT.ecpelginquirytransaction_current_status

结果集



0;Lucindia Evans; 0; 1; 0; 1

0;Lucindia Evans; 0; 0; 57; 57

0;Lucindia Evans; 1; 0; 0; 1



例外输出



0;Lucindia Evans; 1; 1; 57; 59

Result set

0;"Lucindia Evans";0;1;0;1
0;"Lucindia Evans";0;0;57;57
0;"Lucindia Evans";1;0;0;1

The excepted output

0;"Lucindia Evans"; 1;1;57;59

推荐答案

我不会复制您的整个查询，只需使用名称...您可以在下面的空白处添加查询。 />

I will not copy your whole query, just use the names...you add the query in the space below.

SELECT Total_Flag, USER_NAME, SUM(NOT_RESPONDED_COUNT), SUM(FAILED_COUNT), SUM(RESPONDED_COUNT), SUM(TOTAL_COUNT)
FROM
(your query above) qry
GROUP BY 
TOTAL_FLAG, USER_NAME

您可以直接在查询中添加总和，但是对于案例和COALESCE，它可能变得不可读（甚至更多） - 同时，尝试按状态删除分组并输入查询。甚至可能完全。





如果这有帮助请花时间接受解决方案。谢谢。

You could add sums directly into your query, but with cases and COALESCE it could get unreadable (even more then it is) - also, try removing group by status and type in your query. Maybe even completely.


If this helps please take time to accept the solution. Thank you.

这篇关于即使在分组后，我也会获得重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！