tuspazio tuspazio - 9 days ago 7
SQL Question

FOR loop in SQL replacement

I'm new at SQL and one thing that I've just learned reading other posts is that there are no FOR loop constructs in SQL and it is not a good practice trying emulate it somehow.

Anyhow, I've got this problem that I would approach iteratively: a table has 2 columns,

fav_retail_id
and
user_id
. The first column contains the retails added among favorites and the second the users that added the correspondent retail among favorites.

Now, I'd like to know how many users have the retail with id = 42 among their favorite retails. I simply wrote the following:

select count(id) from favorite_retails
where retail_id = 42


And this seems to work pretty well. My point now is that I want to do the same for some other retails, say those with id = 31, 54, 98.

Is there a way to iteratively get this information?
Thanks for support, A.

Answer

You would use group by, if you want the answer for each one:

select retail_id, count(id)
from favorite_retails
where retail_id in ( 31, 54, 98 )
group by retail_id;

Remove the where if you want the answer for all retail_ids.