Prem Prem - 4 months ago 20
SQL Question

Find users according to occurences of notifications_sent mysql

I am trying to get users with their count on notification_template_id.
Means if a user is targeted more than once with the notification_template_id 56.

E.G

user_id notification_template_id count
229 56 3
117 13 2


enter image description here

the query i am trying is:

SELECT
user_id,
notification_template_id,
count(notification_template_id) AS cnt
FROM
user_notifications
GROUP BY
user_id;


but it shows only total count of notifications per user.

Answer

the sql is:

SELECT
    user_id,
    notification_template_id,
    count(notification_template_id) AS count
FROM
    user_notifications
GROUP BY
    user_id,
    notification_template_id;