CODEWITHSUNDEEP
sqlsql-server
Gaurang s
Gaurang s

Reputation: 842

Combine row after used group by in SQL

I have created query like 

SELECT COUNT(CurrentSlideId) AS attempts,
       CurrentSlideId,
       UserLessonProgressId,
       IsHint
FROM  UserAnswers
WHERE (UserID = 169494)
  and UserLessonProgressId=218629 
GROUP BY UserLessonProgressId, CurrentSlideId, IsHint
ORDER BY UserLessonProgressId, CurrentSlideId

and output of above query looks like

enter image description here

now i want to combine last 2 row in one row like

Attempts=2 currentSlideId=19328 UserLessonProgressId=218629 and IsHint=1

Upvotes: 1

Views: 39

Answers (2)

Gaurang s
Gaurang s

Reputation: 842

                        SELECT        COUNT(CurrentSlideId) AS attempts, CurrentSlideId, UserLessonProgressId, max(case when IsHint=1 then 1 else 0 end) hint
                        FROM            UserAnswers
                        WHERE        (UserID = 169494) and UserLessonProgressId=218629 
                        GROUP BY UserLessonProgressId, CurrentSlideId
                        ORDER BY UserLessonProgressId,CurrentSlideId

Upvotes: 0

jarlh
jarlh

Reputation: 44766

Keep your query, but do MAX(IsHINT) and remove that column from GROUP BY:

SELECT COUNT(*) AS attempts,
       CurrentSlideId,
       UserLessonProgressId,
       MAX(IsHint)
FROM  UserAnswers
WHERE UserID = 169494
  and UserLessonProgressId = 218629 
GROUP BY CurrentSlideId, UserLessonProgressId
ORDER BY CurrentSlideId, UserLessonProgressId

Upvotes: 2

Related Questions