I have a table with dates and some data. How can I display data for each month (January, February, March), and also display the amount for three months, the average for three months and the maximum payment in one request?
the result should be like this
users | date | pay | balance | avg(balance) | avg(pay) | sum(pay) |
---|---|---|---|---|---|---|
user_1 | 2016-01-30 | 25 | 32 | 37.6 | 21.3 | 64 |
user_1 | 2016-02-28 | 22 | 39 | 37.6 | 21.3 | 64 |
user_1 | 2016-03-30 | 17 | 42 | 37.6 | 21.3 | 64 |
user_2 | 2016-01-30 | 33 | 35 | 31.6 | 21.3 | 95 |
user_2 | 2016-02-28 | 42 | 29 | 31.6 | 21.3 | 95 |
user_2 | 2016-03-30 | 19 | 31 | 31.6 | 31.3 | 95 |
I can do this with only two requests
select users, date, pay from table
I received data for each monthselect users avg(balance), avg(pay), sum(pay), max(pay) from table group by users
how can I combine these two queries?
Advertisement
Answer
Try:
select t1.users, `date`, pay , avg_balance, avg_pay, sum_pay, max_pay from table_tbl t1 inner join ( select users, avg(balance) as avg_balance, avg(pay) as avg_pay, sum(pay) as sum_pay, max(pay) as max_pay from table_tbl group by users ) as t2 on t1.users=t2.users;