Query with if and declare

Question

How can a query like this be made?

SELECT 
PersonId, 
Name,
(
    DECLARE @p INT=(SELECT (Not1 + Not2 + Not3)/3 FROM Tablo_Not WHERE NotId=PersonId);
    DECLARE @s INT;
    IF @p>84
        SET @s=5;
    ELSE IF @p>69
        SET @s=4;
    ELSE IF @p>54
        SET @s=3;
    ELSE IF @p>44
        SET @s=2;
    ELSE
        SET @s=1;
    SELECT @s;
) AS Degree
FROM Tablo_Person;

Answer 1

You cannot use a DECLARE statement within a SELECT query like that. However, you can achieve the result by using a CASE expression instead of DECLARE and IF statements.

SELECT 
    p.PersonId, 
    p.Name,
    CASE 
        WHEN n.AverageScore > 84 THEN 5
        WHEN n.AverageScore > 69 THEN 4
        WHEN n.AverageScore > 54 THEN 3
        WHEN n.AverageScore > 44 THEN 2
        ELSE 1
    END AS Degree
FROM Tablo_Person p
INNER JOIN (
    SELECT PersonId, (Not1 + Not2 + Not3) / 3 AS AverageScore
    FROM Tablo_Not
) n ON p.PersonId = n.PersonId;