SQL: How to do kind of a for loop

Question

I have this SQL query:

SELECT count(commentratings.ID) AS count, user_ID FROM `commentratings`
WHERE comment_ID = ANY (SELECT ID FROM `ratings` WHERE user_ID = 2) AND rating_direction = 1

It returns the number of upvotes for the user with user_ID = 2 (as expected).

I would like to get this count not only for this particular user, but for all users in the database. I get all relevant IDs with

SELECT user_ID from users

How can I alter the first query so that it returns all counts for all users? It would have to be some kind of for loop, I suppose, which loops over the ID in the subquery. Pseudocode:

for i in (SELECT user_ID from users):
   SELECT count(commentratings.ID) AS count, user_ID FROM `commentratings`
   WHERE comment_ID = ANY (SELECT ID FROM `ratings` WHERE user_ID = i) AND 
   rating_direction = 1

How do I implement this with SQL? Thanks a lot!

SQL is not like object oriented programming. In general, if you're trying to use a loop, you're doing it wrong. Try to think in terms of sets of results, not individual rows. A single query using a `GROUP BY` clause will do what you want. — Patrick Tucci, Apr 09 '19 at 14:54

score 0 · Answer 1 · edited Apr 09 '19 at 16:52

Following your original query, you can do another subquery:

SELECT count(commentratings.ID) AS count, user_ID 
FROM `commentratings`
WHERE comment_ID = ANY (SELECT ID 
                    FROM `ratings` 
                    WHERE user_ID in (SELECT user_ID from users)) 
AND rating_direction = 1

Although, I think your first query would be better with a join, but I might not completely get what the database model is.

score 0 · Answer 2 · answered Apr 09 '19 at 14:54

0

Try something like:

select users.user_id,
       count(*) num_upvotes
from users
     left outer join ratings on ratings.user_id = users.user_id
where ratings.rating_direction = 1
group by users.user_id

answered Apr 09 '19 at 14:54

Saad Ahmad

393
1
7

score 0 · Answer 3 · answered Apr 09 '19 at 14:59

If you want to iterate through each value of a column like a for loop you can use the cursor

It looks like this

DECLARE @UserID int
DECLARE UserIDs CURSOR LOCAL FOR SELECT user_ID from users

OPEN UserIDs 
FETCH NEXT FROM UserIDs into @UserID 
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT count(commentratings.ID) AS count, user_ID FROM `commentratings`
WHERE comment_ID = ANY (SELECT ID FROM `ratings` WHERE user_ID = @UserID) AND rating_direction = 1

    FETCH NEXT FROM UserIDs into @UserID 
END

CLOSE UserIDs 
DEALLOCATE UserIDs

Iterate through rows in SQL Server 2008

SQL: How to do kind of a for loop

3 Answers3