How select from another table without duplicates?

I have such query:

SELECT activity.login_time, sum(activity.sessions) FROM activity
inner JOIN payments ON payments.user_id = activity.user_id
WHERE activity.login_time BETWEEN '2018-04-05' and '2018-04-18'
GROUP BY activity.login_time;


I've got two tables (columns: activity (user_id, sessions, login_time) and payments(column: user_id). The second one has duplicate values.
Need to select sessions for user_id from "payments" without duplicates.

My variant doesn't avoid duplicates. What should I fix in my query?

Thanks !

activity
user_id login_time sessions
101 2018-04-11 4
102 2018-04-02 1
103 2018-04-15 3
104 2018-04-06 2
104 2018-04-05 6
105 2018-04-16 1
105 2018-04-04 6
105 2018-04-01 14
106 2018-04-19 5
106 2018-04-17 3
106 2018-04-16 3
106 2018-04-15 4
106 2018-04-13 2


Payments

user_id
101
103
104
106


output

login_time sessions
2018-04-05 ?
2018-04-06 ?
2018-04-07 ?
2018-04-08 ?
2018-04-09 ?
2018-04-10 ?
2018-04-11 ?
2018-04-12 ?
2018-04-13 ?
2018-04-14 ?
2018-04-15 ?
2018-04-16 ?
2018-04-17 ?
2018-04-18 ?


1 Answer
1

It seems to me from your description you want user aggregation

SELECT
a.user_id,
SUM(a.sessions) as user_session
FROM
activity a
INNER JOIN
payments p ON p.user_id = a.user_id
WHERE
a.login_time BETWEEN '2018-04-05' AND '2018-04-18'
GROUP BY
a.user_id;


But if you want login date with sum of each user then use subquery

SELECT
a1.login_time, t1.user_session
FROM
activity a1
INNER JOIN
(SELECT
a.user_id,
AVG(a.sessions) AS user_session
FROM
activity a
INNER JOIN
payments p ON p.user_id = a.user_id
WHERE
a.login_time BETWEEN '2018-04-05' AND '2018-04-18'
GROUP BY
a.user_id) AS t1 ON a1.user_id = t1.user_id


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.