I have been banging my head against the wall trying to get the lest subquery in the SELECT to work and Just cannot figure out a way for it to give me any kind of output just for the last one. Code works without it.
SELECT U.Username, COUNT(DISTINCT P.PostID) AS 'Total Posts',
CONVERT(CHAR(10), ISNULL((SELECT SUM(R.Upvote) FROM Ratings R WHERE U.UserID = R.FK_UserID), 0)) AS 'Karma',
CONVERT(CHAR(10), ISNULL((SELECT U.UserID, SUM(R.Upvote) FROM Users JOIN Posts ON Posts.FK_UserID = UserID
JOIN Ratings ON Ratings.FK_PostID = PostID GROUP BY UserID), 0)) AS 'Total Karma Received'
FROM Users U
FULL OUTER JOIN Ratings R on U.UserID = R.FK_UserID
FULL OUTER JOIN Posts P on U.UserID = P.FK_UserID
GROUP BY U.Username, U.UserID
ORDER BY 'Total Posts' DESC
CodePudding user response:
The subquery expression for 'Total Karma Received' should return only one value, but your subquery: SELECT U.UserId, SUM(R.UpVote)...GROUP BY UserId is poised to return at least two values (UserId and a Sum), but also multiple rows. What is it supposed to return: 'Total UpVotes for all Users', or 'Total UpVotes for this User', or ...?
CodePudding user response:
You can move userID
to a WHERE in the inner query if you only want the upvotes for this user, or remove it entirely if you want the sum of all upvotes for all users.
SELECT U.Username, COUNT(DISTINCT P.PostID) AS 'Total Posts', CONVERT(CHAR(10), ISNULL( (SELECT SUM(R.Upvote) FROM Ratings R WHERE U.UserID = R.FK_UserID), 0)) AS 'Karma', CONVERT(CHAR(10), ISNULL( (SELECT SUM(R.Upvote) FROM Users JOIN Posts ON Posts.FK_UserID = UserID JOIN Ratings ON Ratings.FK_PostID = PostID WHERE UserID = U.UserID GROUP BY UserID), 0)) AS 'Total Karma Received' FROM Users U FULL OUTER JOIN Ratings R on U.UserID = R.FK_UserID FULL OUTER JOIN Posts P on U.UserID = P.FK_UserID GROUP BY U.Username, U.UserID ORDER BY 'Total Posts' DESC;
db<>fiddle here
CodePudding user response:
It will be simpler and more efficient to join to the tables than to run a sub-query per row.
SELECT U.userID, U.Username, COUNT(DISTINCT P.PostID) AS 'Total Posts', SUM(given.upvote) AS 'Total Karma Given', SUM(recd.upvote) AS 'Total Karma Received' FROM Users U LEFT JOIN posts P ON U.userID = P.FK_UserID LEFT JOIN ratings recd ON P.postID= recd.FK_PostID LEFT JOIN ratings given ON U.userID = given.FK_UserID GROUP BY U.userID,U.username ORDER BY U.userID,U.username GO
userID | Username | Total Posts | Total Karma Given | Total Karma Received -----: | :--------- | ----------: | ----------------: | -------------------: 1 | user one | 2 | null | 1 2 | user two | 1 | null | null 3 | User Three | 0 | null | null 4 | User Four | 0 | 1 | null
db<>fiddle here
CodePudding user response:
one of your sub queries return more than one record, though you get the error