I am facing issues in selecting distinct users and results

lakshmana Babu

9y1.4kSQL

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sourabh Somani9y ago
Accepted answer
This script os for MSSQL (SQL Server).

You can check which keyword is not supported by MySQL and replace that as MySQL

and accept my answer if you get your answer because it will be helpful for others next time.

Thanks
lakshmana Babu9y ago
Hi I have modified the provided query and working fine , BUT i found the query is fetching issue id having status in closed although the issue id has status in pending

Issue id user Question response results status
1        A             Q1                 Y         PASS     closed
1         A            Q2                 y          PASS     closed
2        A            Q1 y           PASS     closed
2       A             Q1                 N           NA        Pending

user count(distict(issue id) ) pass(count) Fail (count) %of PASS
A         1    1                   0               100%

Is there any way to condider only issue id that has status as closed
lakshmana Babu9y ago
Hi ,

Thank you for the script , but again I am getting error as unexpected token error at mysql database.
Sourabh Somani9y ago
Here is the script
create table #temp(Issueid
int ,[user] varchar(30)
,Question varchar(30)
,response varchar(30)
,results varchar(30)
,[status] varchar(30))

insert into #temp
values('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')



select distinct COUNT(*) OVER() as DistinctIssueId,[user],
sum(PassCount) over() as _PassCount,
sum(FailCount) over() as _FailCount,
(sum(PassCount) over()*100)/COUNT(*) OVER() as PassCountPercent
from (SELECT distinct(issueid),[user],case when results='PASS' then 1 else 0 end as PassCount,
case when results='FAIL' then 1 else 0 end as FailCount
from #temp where status='closed') t