Group BY in SELECT CASE SQL

Question

hi i have a query that return YES or NOT for each user now in result i want to add StudentId for showing better result how i can do this?

WITH Prev AS
(
    SELECT StudentId, ISNULL(SUM(Score),0) As HighScoreUser
FROM (SELECT StudentId, Score FROM tbl_ActPoint
UNION ALL
      SELECT StudentId, Score FROM tbl_EvaPoint     WHERE Date>='1396/01/01' and Date <= '1396/01/31'
      ) as T 
      GROUP BY  StudentId
),
Cur AS 
(
    SELECT StudentId, ISNULL(SUM(Score),0) As HighScoreUser
FROM (SELECT StudentId, Score FROM tbl_ActPoint
UNION ALL
      SELECT StudentId, Score FROM tbl_EvaPoint     WHERE Date>'1396/02/01' and Date <= '1396/02/31'
      ) as T 
      GROUP BY  StudentId
)
SELECT CASE 
        WHEN(Prev.HighScoreUser <= Cur.HighScoreUser)
        THEN 'Yes'
        ELSE 'No'
        END as HaveGift
FROM Prev
INNER JOIN Cur
ON Prev.StudentId = Cur.StudentId

what is your input dataset and what is your expected output? — Kannan Kandasamy, Jun 10 '17 at 15:15

score 0 · Accepted Answer · answered Jun 10 '17 at 15:16

0

It's simple. Just add column:

SELECT CASE 
        WHEN(Prev.HighScoreUser <= Cur.HighScoreUser)
        THEN 'Yes'
        ELSE 'No'
        END as HaveGift,
        Prev.StudentId
FROM Prev
INNER JOIN Cur
ON Prev.StudentId = Cur.StudentId

answered Jun 10 '17 at 15:16

Backs

24,430
5
58
85

Group BY in SELECT CASE SQL

1 Answers1