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 tableI 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;