How to calculate average result for certain date?

Could you help me? I have two tables.

Into the first table (activity) there are: user_id, sessions and login_time.

Into the second (payments) there's only one column - user_id.

Here's me query:

SELECT activity.login_time, activity.user_id, avg(activity.sessions) as
user_sessions
FROM activity
inner JOIN payments ON payments.user_id = activity.user_id
WHERE activity.login_time ='2018-04-05' group by activity.user_id;


Using this query, I get such table:

+------------+---------+---------------
| login_time | user_id | user_sessions
+------------+---------+---------------
| 2018-04-05 | 107 | 12.0000
| 2018-04-05 | 110 | 1.0000
| 2018-04-05 | 112 | 5.0000
| 2018-04-05 | 115 | 5.0000
| 2018-04-05 | 117 | 7.0000
| 2018-04-05 | 120 | 1.0000
| 2018-04-05 | 123 | 1.0000
...


How should I make a query to get average:

+------------+------------
| login_time | avg_user_sessions
+------------+---------
| 2018-04-05 | 4,57


Note: difficulty is in that user_id has duplicates

Tables

user_id login_time sessions
107 2018-04-05 12
110 2018-04-05 1
112 2018-04-05 5
115 2018-04-05 5
117 2018-04-05 7
120 2018-04-05 1
123 2018-04-05 1



user_id
107
107
107
110
112
115
115
117
120
123


1 Answer
1

If there are many user_id duplicates in payments table, you can try to use DISTINCT in your user_id from payments table.

user_id

payments

DISTINCT

user_id

payments

but in your case, You can only select activity directly, don't need to join with payments, because you didn't get any column from it.

activity

join

payments

CREATE TABLE activity(
login_time date,
user_id int,
sessions float
);

CREATE TABLE payments (
user_id INT
);



INSERT INTO payments VALUES (107);
INSERT INTO payments VALUES (107);
INSERT INTO payments VALUES (110);
INSERT INTO payments VALUES (112);
INSERT INTO payments VALUES (115);
INSERT INTO payments VALUES (115);
INSERT INTO payments VALUES (117);
INSERT INTO payments VALUES (120);
INSERT INTO payments VALUES (123);





INSERT INTO activity VALUES ('2018-04-05',107,12);
INSERT INTO activity VALUES ('2018-04-05',110,1);
INSERT INTO activity VALUES ('2018-04-05',112,5);
INSERT INTO activity VALUES ('2018-04-05',115,5);
INSERT INTO activity VALUES ('2018-04-05',117,7);
INSERT INTO activity VALUES ('2018-04-05',120,1);
INSERT INTO activity VALUES ('2018-04-05',123,1);


Query 1:

SELECT a.login_time, avg(a.sessions) as
user_sessions
FROM activity a
inner JOIN (SELECT DISTINCT user_id FROM payments) p ON p.user_id = a.user_id
WHERE a.login_time ='2018-04-05'
group by a.login_time


Results:

| login_time | user_sessions |
|------------|-------------------|
| 2018-04-05 | 4.571428571428571 |


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.