Getting and comparing averages from MySQL with PHP

Question

I want to be able to contrast "last weeks" data against "this weeks" data and get the percent change. (i.e. +1.2% or -.5%)

Here's an example of the MySQL table:

Date           | Happy | Sad | Angry | Fearful
2016-04-01 |     2     |   1   |     3    |    0
2016-04-02 |     3     |   1   |     3    |    1
2016-04-03 |     0     |   4   |     1    |    2
2016-04-04 |     1     |   3   |     2    |    1

So pretending that there are at least 14 rows here how would I go about getting the average of the first 7 days, the average of the previous 7 days, and then creating the comparison that shows the percentage difference?

I can get the most recent 7 days averages with the code below, but when I try and repeat it and change the offset it fails:

    SELECT AVG(happy), AVG(sad), AVG(angry), AVG(fearful)
      FROM table_name
      LIMIT 0, 7

Check out [this question](http://stackoverflow.com/questions/14051057/mysql-date-function-not-working-for-less-than) about less than a date in mysql — larsAnders, Apr 07 '16 at 23:57
As a note you can create tabular data by indenting four spaces to put it in fixed-width mode. There's no need to use ` ` to try and line things up. — tadman, Apr 08 '16 at 00:11

Barmar · Accepted Answer · 2016-04-08T00:44:07.600

2

Use subqueries to calculate the averages for each week, using date ranges, and join them.

SELECT curWeek.happy AS curHappy, curWeek.sad AS curSad, curWeek.angry AS curAngry, curWeek.fearful AS curFearful,
       prevWeek.happy AS prevHappy, prevWeek.sad AS prevSad, prevWeek.angry AS prevAngry, prevWeek.fearful AS prevFearful,
       100*(curWeek.happy - prevWeek.happy)/prevWeek.happy AS happyChange,
       100*(curWeek.sad - prevWeek.sad)/prevWeek.sad AS sadChange,
       100*(curWeek.angry - prevWeek.angry)/prevWeek.angry AS angryChange,
       100*(curWeek.fearful - prevWeek.fearful)/prevWeek.fearful AS fearfulChange
FROM (SELECT AVG(happy) AS happy, AVG(sad) AS sd, AVG(angry) AS angry, AVG(fearful) AS fearful
      FROM tablename
      WHERE date > NOW() - INTERVAL 1 WEEK) AS curWeek
JOIN (SELECT AVG(happy) AS happy, AVG(sad) AS sd, AVG(angry) AS angry, AVG(fearful) AS fearful
      FROM tablename
      WHERE date BETWEEN NOW() - INTERVAL 2 WEEK AND NOW() - INTERVAL 1 WEEK) AS prevWeek

edited Apr 08 '16 at 00:44

answered Apr 08 '16 at 00:10

Barmar

741,623
53
500
612

nice solution @Barmar – gavgrif Apr 08 '16 at 00:17
ok, wow. So I'm feeling like I overstated my abilities when I deemed myself solidly beginner. How do I use the aliases? Are they just like a variable? – Bradbains Apr 08 '16 at 00:23
Yeah, aliases are like variables that refer to tables, subqueries, or columns. – Barmar Apr 08 '16 at 00:24
How would I "echo", lets say current week angry in the body of my page? – Bradbains Apr 08 '16 at 00:41
I've updated the query so it returns the current and previous week averages, not just the changes. So you can use `echo $row['curAngry'];` – Barmar Apr 08 '16 at 00:44
Awesome! Thank you everyone! – Bradbains Apr 08 '16 at 00:47

Getting and comparing averages from MySQL with PHP

1 Answers1