Forums - C# Corner

Forum guidelines
lakshmana Babu

lakshmana Babu

  • 1.5k
  • 6
  • 407

I am facing issues in selecting distinct users and results

Jul 5 2017 10:13 PM
I Have a table as below
 
Issue id      user           Question       response   results    status
1                 A                Q1                   y             PASS     closed
1                 A                Q2                   y             PASS      closed
1                 A                Q3                   y             PASS     closed
2                 A                Q1                   N            FAIL       closed
2                 A                Q2                   N             FAIL      closed
2                 A                Q3                  N              FAIL      closed
3                  A                Q1                 Y               PASS     pending
3                  A                Q2                  N             FAIL       pending
 
My result should look like below
 
user   count(distict(issue id) )   pass(count)   Fail (count)   %of PASS
A                2                                   1                 1                    50%
 
I tried below query but it end up in error 
 
SELECT a.user, COUNT(*) as total, SUM(CASE WHEN result = 'PASS' THEN 1 ELSE 0 END) as PASSED, SUM(CASE WHEN result = 'FAIL' THEN 1 ELSE 0 END) as FAILED FROM tbl_main a INNER JOIN tbl_main ON a.id = tbl_main.id where status IN('CLOSED')GROUP BY resolved_by  

Answers (4)