I am getting duplicate values even after group by

Question

1.00/5 (1 vote)

See more:

SQL

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

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

Posted 12-Jan-15 18:34pm

Member 10256268

Updated 12-Jan-15 18:38pm

v2

Add a Solution

Comments

Sinisa Hajnal 13-Jan-15 3:46am

Use SUM on the last four fields.

Member 10256268 13-Jan-15 3:51am

Can give example , i am not getting what you said

Member 10256268 13-Jan-15 3:58am

can you modify query please.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sinisa Hajnal · Answer 1 · 2015-01-12T22:12:00

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

SQL

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

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.