CODEWITHSUNDEEP
mysqlsqljoinselect
Елисей Горьков
Елисей Горьков

Reputation: 447

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

Upvotes: 1

Views: 34

Answers (1)

D-Shih
D-Shih

Reputation: 46239

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

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.

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 |

Upvotes: 2

Related Questions