1

I have a simple query:

SELECT COUNT(ud.UserID), COUNT(DISTINCT ud.ProductID)
FROM users_data AS ud
    JOIN products AS t ON t.ID = ud.ProductID
WHERE ud.UserID = '3'

Which results in:

COUNT(ud.UserID)    COUNT(DISTINCT ud.ProductID)
519                 425

When I try to include in a while or for loop:

DELIMITER //

SET @i = 0;
FOR i IN 1..10
DO SELECT COUNT(ud.UserID), COUNT(DISTINCT ud.ProductID)
  FROM users_data AS ud
    JOIN products AS t ON t.ID = ud.ProductID
  WHERE ud.UserID = (i)
END FOR
//

I get no output other than:

Query executed OK, 0 rows affected.

Is there something else I'm misssing? thank you.

ctfd
  • 338
  • 3
  • 14

2 Answers2

1

Why do you want to use a loop? This is much better to do as a simple query:

SELECT ud.UserId, COUNT(*), COUNT(DISTINCT ud.ProductID)
FROM users_data ud 
GROUP BY ud.UserID;

If you want specific users, you can use a WHERE clause:

SELECT ud.UserId, COUNT(*), COUNT(DISTINCT ud.ProductID)
FROM users_data ud 
WHERE ud.UserId IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
GROUP BY ud.UserID;

Note: I don't think the JOIN is necessary.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • My intention was to iterate through a range of users (like 1-10). When I try your example it seems to take a very long time (I'm still waiting over 10 minutes). It's a huge database, so if there's a way to quickly return a range of users that's going to work better, thank you! – ctfd Feb 15 '19 at 00:30
  • Thank you very much, that works much better! (I ended up using `BETWEEN 1 AND 10` instead of `1, 2, 3...` because I'll need to specify different ranges. – ctfd Feb 15 '19 at 00:43
0

The Answer to the question asked is in the definition of DO. It is defined to perform the query, then throw away the results.

Rick James
  • 135,179
  • 13
  • 127
  • 222