Skip to content
Advertisement

how to combine queries from one table?

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

  1. select users, date, pay from table I received data for each month

  2. select 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;  

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

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement