Checking for value in list of rows per user-CodePudding

I'm currently working with a list of survey data:

UserID	User Name	SurveyID	QuestionID	ResponseID	isSkipped
1	Test1	100	10	1	0
1	Test1	100	20	2	0
2	Test2	101	10	3	0
2	Test2	101	20	4	1
3	Test3	102	10	5	1
3	Test3	102	20	6	1

I'm looking for a query to give me the user, the SurveyID, and a flag (0 = Complete/1 = Not Complete) telling me if they completed all the questions (all the isSkipped values are 0)... so the end should be....

UserID	User Name	SurveyID	Complete
1	Test1	100	0
2	Test2	101	1
3	Test3	102	1

Can anyone help me out? I've tried using GROUP BY and SUM/COUNT and such, but I'm always getting more than one row per user. I'm sure it's something simple I'm missing.

CodePudding user response：

Presumably, for your required result, you require:

select UserId, User_Name, SurveyId, 
  case when sum(isSkipped) > 0 then 1 else 0 end as Complete
from t
group by UserId, User_Name, SurveyId;

CodePudding user response：

Since it's enough they completed one question to be considered complete then you can just choose max(isSkipped) for your complete status.

select   UserID
        ,[User Name]
        ,SurveyID
        ,max(isSkipped) as Complete
from     t
group by UserID, [User Name], SurveyID

UserID	User Name	SurveyID	Complete
1	Test1	100	0
2	Test2	101	1
3	Test3	102	1

Fiddle