Creating a running total output as an array

[AasimAzam]

Hi,

I have a database that has the following layout (only relevant colums detailed

 

index

season

day

month

year

goals_scored

 

I want to create an array with a running total of goals scored by a quarter. For example,

 

Sum of goals scored between

month=8 to month=10

month=11 to month=1

month=2 to month=4

month=5 to month=7

Where year=2002

then go through years 2002-current year what ever it is and it up.

 

 

So if I have

sum of month=8-10: 0

sum of month=11-1: 2

sum of month=2-4: 8

sum of month=5-7: 5

 

When I echo the array it will be in this format:

 

(0,2,10,15)

 

I also want to make a separate query for a single year, by month, i.e

 

sum of goals scored where month=7, month=8 etc.

 

Any indication and help explaining the function for this would help. My php is very basic, I know how create a query and make loop to echo a table of results etc. These arrays are for graphs. I want the graph to be ready for the full season even if its not happened yet.

 

 

[Barand]

I am assuming you data looks something like this

mysql> SELECT * FROM goal;
+---------+-----------+------+-------+------+--------------+
| goal_id | season    | day  | month | year | goals_scored |
+---------+-----------+------+-------+------+--------------+
|       1 | 2002-2003 |    1 |     8 | 2002 |            5 |
|       2 | 2002-2003 |   31 |    10 | 2002 |            8 |
|       3 | 2002-2003 |    1 |    11 | 2002 |            9 |
|       4 | 2002-2003 |   31 |     1 | 2003 |            4 |
|       5 | 2002-2003 |    1 |     2 | 2003 |            8 |
|       6 | 2002-2003 |   30 |     4 | 2003 |           10 |
|       7 | 2002-2003 |    1 |     5 | 2003 |            9 |
|       8 | 2002-2003 |   31 |     7 | 2003 |           12 |
|       9 | 2003-2004 |   10 |     8 | 2003 |           15 |
|      10 | 2003-2004 |    5 |    11 | 2003 |           10 |
|      11 | 2003-2004 |    1 |     7 | 2004 |            6 |
+---------+-----------+------+-------+------+--------------+

Set up a second table to define which months are in each quarter

mysql> SELECT * FROM quarter;
+------------+---------+-------+
| quarter_id | quarter | month |
+------------+---------+-------+
|          1 | Q1      |     8 |
|          2 | Q1      |     9 |
|          3 | Q1      |    10 |
|          4 | Q2      |    11 |
|          5 | Q2      |    12 |
|          6 | Q2      |     1 |
|          7 | Q3      |     2 |
|          8 | Q3      |     3 |
|          9 | Q3      |     4 |
|         10 | Q4      |     5 |
|         11 | Q4      |     6 |
|         12 | Q4      |     7 |
+------------+---------+-------+

Then you can run a query to get your quarter totals

SELECT g.season
    , q.quarter
    , SUM(g.goals_scored) as goals
FROM goal g
INNER JOIN quarter q USING (month)
GROUP BY season, quarter

Giving

+-----------+---------+-------+
| season    | quarter | goals |
+-----------+---------+-------+
| 2002-2003 | Q1      |    13 |
| 2002-2003 | Q2      |    13 |
| 2002-2003 | Q3      |    18 |
| 2002-2003 | Q4      |    21 |
| 2003-2004 | Q1      |    15 |
| 2003-2004 | Q2      |    10 |
| 2003-2004 | Q4      |     6 |
+-----------+---------+-------+