how to combine queries from one table?-CodePudding

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 month
select users avg(balance), avg(pay), sum(pay), max(pay) from table group by users

how can I combine these two queries?

CodePudding user response：

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;

Demo: https://www.db-fiddle.com/f/pB6b5xrgPKCivFWcpQHsyE/9

CodePudding user response：

select * from table a left join (select users avg(balance), avg(pay), sum(pay), max(pay) from table group by users) b on a.users = b.users

CodePudding user response：

you can use join:

select t1.users, date, pay, avgBalance, avgPay, sumPay, maxPay
from (select users, date, pay from table) t1 join 
(select users avg(balance) avgBalance, avg(pay) avgPay, sum(pay) sumPay, max(pay) maxPay from table group by users) t2 on t1.users = t2.users