Need help with SQL

Question

I have the following query that seems to do what I want it to, but I think there may be a shorter way

CREATE TABLE #userScoreForComment (
        comment_id int,
        points int
    )

    INSERT INTO #userScoreForComment
    SELECT comment_id, SUM(Scores.points) AS Points
    FROM Comments INNER JOIN Scores ON Comments.username = Scores.username
    WHERE Comments.upload_id = @uploadid
    GROUP BY comment_id

    SELECT Comments.comment_id, username, comment_text, post_date, Points
    FROM Comments JOIN #userScoreForComment on Comments.comment_id = #userScoreForComment.comment_id
    WHERE upload_id = @uploadid

My attempt at making this shorter has failed, here's what I have

SELECT Comments.comment_id, username, comment_text, post_date,
        Points AS (SELECT SUM(Scores.points) FROM Scores WHERE Score.username = Comments.username)
    FROM Comments
    WHERE Comments.upload_id = @uploadid
    GROUP BY Comments.comment_id

SQL server is telling me Incorrect syntax near the keyword 'FROM'.

Thanks

Answer 1

(SELECT SUM(Scores.points) FROM Scores WHERE Score.username = Comments.username) AS Points

Points after subselect ! Points will be the name of that SUM() column.

Answer 2

It's a bit neater to use a join, rather than a nested SELECT, to calculate the SUM:

SELECT Comments.comment_id, Comments.username, comment_text, post_date, SUM(Scores.points)
FROM Comments INNER JOIN Scores ON Comments.username = Scores.username
WHERE upload_id = @uploadid
GROUP BY comment_id, Comments.username, comment_text, post_date