Tirux Tirux - 5 months ago 9
MySQL Question

How can I take average and specific data in a single query in MySQL?

I am trying to get an average and specific data from a table in a single query. The table "Billing" is like the following:

-------------------------------------------
| UserID | ZipCode | Amount | Date |
-------------------------------------------
| 14 | 55520 | 777 | 14/06/2016 |
| 14 | 55520 | 250 | 04/05/2016 |
| 15 | 55520 | 340 | 14/06/2016 |
| 15 | 55520 | 300 | 12/05/2016 |
-------------------------------------------


I can get the specific data of user 14 with this (unfortunately my Date column is a string so hence the STR_TO_DATE function):

select DATE_FORMAT(STR_TO_DATE(Date, '%d/%m/%Y'), '%m/%Y') as Date, Amount as 'Your Amount' from Billing where UserID = 14 order by Date asc;


With the following results:

-------------------------
| Date | Your Amount |
-------------------------
| 05/2016 | 250 |
| 06/2016 | 777 |
-------------------------


And I can get the average of zip code 55520 with this:

select DATE_FORMAT(STR_TO_DATE(Date, '%d/%m/%Y'), '%m/%Y') as Date, avg(Amount) as 'Average Amount' from Billing where ZipCode = 55520 group by Date;


The results are:

----------------------------
| Date | Average Amount |
----------------------------
| 05/2016 | 275.00 |
| 06/2016 | 558.50 |
----------------------------


But I need it in a single query in order to use it in a chart. So I try the following:

select DATE_FORMAT(STR_TO_DATE(Date, '%d/%m/%Y'), '%m/%Y') as Date, Amount as 'Your Amount', (select avg(Amount) from Billing where ZipCode = 55520 group by Date) as 'Average Amount'from Billing where UserID = 14 group by Date order by Date asc;


But I get the "subquery returns more than 1 row" error now. How can I fix this? Or is there a better way of getting the data I want? Basically I need the results like this:

------------------------------------------
| Date | Your Amount | Average Amount |
------------------------------------------
| 05/2016 | 250 | 275.00 |
| 06/2016 | 777 | 558.50 |
------------------------------------------

Answer

You have to use correlation in the subquery:

select DATE_FORMAT(STR_TO_DATE(Date, '%d/%m/%Y'), '%m/%Y') as Date, 
       Amount as 'Your Amount',
       (select avg(Amount) as 'Average Amount' 
        from Billing as t2
        where DATE_FORMAT(STR_TO_DATE(t2.`Date`, '%d/%m/%Y'), '%m/%Y') =
              DATE_FORMAT(STR_TO_DATE(t1.`Date`, '%d/%m/%Y'), '%m/%Y')
              AND ZipCode = 55520 
        group by DATE_FORMAT(STR_TO_DATE(`Date`, '%d/%m/%Y'), '%m/%Y')) as 'Average Amount'
from Billing as t1
where UserID = 14 
order by Date asc;

Demo here