SQL count how much field from record is showed in other table-CodePudding

I have two tables, the first one is USERS and the second one is TAGS.
this is the USERS table fields:

ID
NAME

and this is the TAGS table fields:

PICTURE_ID
USER_ID

now I want to count how much times one of the users is "taged" in the TAGS table (both based on the user_id but I want to be able to check according to the name).

example:
if I have the user
ID=1 NAME="aba"
and in the TAGS I have
PICTURE_ID=8 USER_ID=1,
PICTURE_ID=3 USER_ID=1.
so the number of times "aba" is taged in pictures is 2.

CodePudding user response：

You could use a simple correlated aggregation here -

select u.Name, coalesce((select count(*) from Tags t where t.User_Id = u.Id),0) Tags
from Users u
where u.Id = 'aba';

CodePudding user response：

SELECT COUNT(T.PICTURE_ID) AS TAGs_Count, U.NAME
FROM TAGS T
LEFT JOIN USERS U
ON U.ID = T.USER_ID
GROUP BY T.USER_ID, U.NAME

In case if you want to display counts just for specific User (here it's Aba)

SELECT COUNT(T.PICTURE_ID) AS TAGs_Count, U.NAME
FROM TAGS T
LEFT JOIN USERS U
ON U.ID = T.USER_ID
WHERE U.NAME LIKE 'Aba'
GROUP BY T.USER_ID, U.NAME

Or you can use wildcards if you don't know exact names you searching for:

WHERE U.NAME LIKE '