0

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);

Edited by Hakim_2

2
Contributors
1
Reply
25
Views
3 Years
Discussion Span
Last Post by broj1
0

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";

Edited by broj1

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.