0

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.

רועי אטיאס
  • 79
  • 6

2 Answers2

0
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 '%ba'

or

WHERE U.NAME LIKE 'Ab%'

or for both ends:

WHERE U.NAME LIKE '%b%'

If you want to have search case insensitive you use this:

WHERE UPPER(U.NAME) LIKE UPPER('Aba')

Also consider what operator actually you want to use. LIKE and Equals ( = ) are not exactly the same.

More about that you'll find here

Amikot40
  • 38
  • 7
  • the count working but I want to give a name to count and not just print all of the users – רועי אטיאס Mar 14 '22 at 17:17
  • Could you explain better what actually you want to get? Both mine and Stu's scripts are giving you count of tags with the name associated to these counts. Do you want to search for user ? – Amikot40 Mar 14 '22 at 17:29
  • I want to write in the script the name of the user that I want to search for, for example if I want to get the count of "aba" so I write in the script to get me only the count of "aba" – רועי אטיאס Mar 14 '22 at 17:42
0

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';
Stu
  • 30,392
  • 6
  • 14
  • 33