What can I do to fix SQL error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

Question

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

Answer 1

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 ...?

Answer 2

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

Answer 3

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

Answer 4

one of your sub queries return more than one record, though you get the error. some thing like this:

SELECT A.*,
   CONVERT(CHAR(10), ISNULL(SUM(R.Upvote), '0')) AS 'Total KarmaReceived' FROM (
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'
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) A
JOIN Posts
    ON Posts.FK_UserID = A.UserID
JOIN Ratings
    ON Ratings.FK_PostID = PostID GROUP BY UserID;