How to get average value based on user selection?

[Hakim_2]

I manage to display data using below query that will show value for each month. How to calculate average value starting july onwards(based on user selection).

Let say the value for element 'A' are Jul=66, Aug= 65, Sep=87. If user select Aug then the average is 65.5 and if user select Sep then the average is 72.6

Output:

Element | Jan | Feb | Mar |Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dis
==============================================================================
A       | 78  | 65  | 44   |67 | 76  | 88  | 66  | 65  | 87  | 90  | 56  | 70
B       | 78  | 65  | 44   |67 | 76  | 88  | 66  | 65  | 87  | 90  | 56  | 70
C       | 78  | 65  | 44   |67 | 76  | 88  | 66  | 65  | 87  | 90  | 56  | 70

My query:

$sql2 = "Select element,
SUM(CASE WHEN Month = 'Jan' THEN total ELSE 0 END ) AS Jan,
SUM(CASE WHEN Month = 'Feb' THEN total ELSE 0 END ) AS Feb,
SUM(CASE WHEN Month = 'Mac' THEN total ELSE 0 END ) AS Mac,
SUM(CASE WHEN Month = 'Apr' THEN total ELSE 0 END ) AS Apr,
SUM(CASE WHEN Month = 'May' THEN total ELSE 0 END ) AS May,
SUM(CASE WHEN Month = 'Jun' THEN total ELSE 0 END ) AS Jun,
SUM(CASE WHEN Month = 'Jul' THEN total ELSE 0 END ) AS Jul,
SUM(CASE WHEN Month = 'Aug' THEN total ELSE 0 END ) AS Aug,
SUM(CASE WHEN Month = 'Sep' THEN total ELSE 0 END ) AS Sep,
SUM(CASE WHEN Month = 'Oct' THEN total ELSE 0 END ) AS Oct,
SUM(CASE WHEN Month = 'Nov' THEN total ELSE 0 END ) AS Nov,
SUM(CASE WHEN Month = 'Dec' THEN total ELSE 0 END ) AS Dis
FROM tbl_ma GROUP BY element";
$rs2 = mysql_query($sql2);
$getRec2 = mysql_fetch_assoc($rs2);

[broj1]

Something like:

$rs2 = mysql_query($sql2);
// array of months to get the index
$months = array('Jan' => 1,'Feb' => 2,'Mar' => 3,'Apr' => 4, 'May' => 5, 'Jun' => 6, 'Jul' => 7, 'Aug' => 8, 'Sep' => 9, 'Oct' => 10, 'Nov' => 11, 'Dis' => 12);
// the month we selected and its index
$selectedMonth = 'Nov';
$selectedMonthIndex = $months[$selectedMonth];
// the query will return three rows
// let's put them in a temporary associative array containing only grand total for selected month and above
$tempArr = array();
while($row = mysql_fetch_assoc($rs2)) {
    // temp key for the associative array
    $tempKey = $row['element'];
    // construct a temporary array
    foreach($row as $key => $val) {
        // skip the element field and fields below the selected month
        if($months[$key] == 'element' || $months[$key] < $selectedMonthIndex) {
            continue;
        } 
        $tempArr[$tempKey] = isset($tempArr[$tempKey]) ? $tempArr[$tempKey] + $val : $val;
    }
}
// number of months for calculating average
$numberOfMonths = 12 - $selectedMonthIndex + 1;
// calculated averages
$averageA =  $tempArr['A'] / $numberOfMonths;
$averageB =  $tempArr['B'] / $numberOfMonths;
$averageC =  $tempArr['C'] / $numberOfMonths;
// display the averages
echo "Selected month: $selectedMonth<br>";
echo "Averages: A: $averageA, B: $averageB, C: $averageC";