Page 1 of 1

How to get average value based on user selection?

Posted: Wed Nov 19, 2014 1:50 am
by xhkmx
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=80, Aug= 80, Sep=90. If user select Aug then the average is 80 and if user select Sep then the average is 83.

Query:

Code: Select all

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

Below is how print the data:

Code: Select all

<?php 
	while ($row = mysql_fetch_assoc($rs2)) {						
	?>
		<tr>
			<td style="color:black;background-color:#f5efef"><div align="left"><?php echo $row['element']; ?></div></td>	
			<?php if( $year != '2014' ){ ?>
			<?php if( 1 <= $monthID ): ?><td><div align="center" style="color:black"><?php echo $row['Jan']; ?></div></td><?php endif; ?>
			<?php if( 2 <= $monthID ): ?><td><div align="center" style="color:black"><?php echo $row['Feb']; ?></div></td><?php endif; ?>
			<?php if( 3 <= $monthID ): ?><td><div align="center" style="color:black"><?php echo $row['Mac']; ?></div></td><?php endif; ?>
			<?php if( 4 <= $monthID ): ?><td><div align="center" style="color:black"><?php echo $row['Apr']; ?></div></td><?php endif; ?>
			<?php if( 5 <= $monthID ): ?><td><div align="center" style="color:black"><?php echo $row['May']; ?></div></td><?php endif; ?>
			<?php if( 6 <= $monthID ): ?><td><div align="center" style="color:black"><?php echo $row['Jun']; ?></div></td><?php endif; ?>
			<?php } ?>
			<?php if( 7 <= $monthID ): ?><td><div align="center" style="color:black"><?php echo $row['Jul']; ?></div></td><?php endif; ?>
			<?php if( 8 <= $monthID ): ?><td><div align="center" style="color:black"><?php echo $row['Aug']; ?></div></td><?php endif; ?>
			<?php if( 9 <= $monthID ): ?><td><div align="center" style="color:black"><?php echo $row['Sep']; ?></div></td><?php endif; ?>
			<?php if( 10 <= $monthID ): ?><td><div align="center" style="color:black"><?php echo $row['Oct']; ?></div></td><?php endif; ?>
			<?php if( 11 <= $monthID ): ?><td><div align="center" style="color:black"><?php echo $row['Nov']; ?></div></td><?php endif; ?>
			<?php if( 12 <= $monthID ): ?><td><div align="center" style="color:black"><?php echo $row['Dis']; ?></div></td><?php endif; ?>
			<td><div align="center" style="color:black"><?php //echo average; ?></div></td>	
			<td><div align="center" style="color:black"><?php //echo Rating; ?></div></td>	
		</tr>                                  
<?php } ?>

Re: How to get average value based on user selection?

Posted: Wed Nov 19, 2014 12:57 pm
by requinix
You can build an array of all the values, starting with July's,

Code: Select all

$totals = array($row['Jul'], $row['Aug'], $row['Sep'], /* etc */);
Then decide how many of those you want to keep: if the user chose August then you want 2. I don't know how you're getting the choice but the code for that might look like

Code: Select all

$count = array_search($choice, array("Jul", "Aug", "Sep", /* etc */)) + 1; // choice = Aug, count = 2
Remember to validate the input first (must be "Jul" or "Aug" or whatever) or you'll get unexpected results.
array_slice can give you a part of that earlier array

Code: Select all

$subset = array_slice($totals, 0, $count);
and getting the average is just a matter of some quick math.

Code: Select all

$average = array_sum($subset) / $count;

Re: How to get average value based on user selection?

Posted: Wed Nov 19, 2014 2:11 pm
by Christopher
xhkmx wrote:Let say the value for element 'A' are Jul=80, Aug= 80, Sep=90. If user select Aug then the average is 80 and if user select Sep then the average is 83.
Stepping back a little, I get the impression that if you stored the month as a number 1-12 that it would make queries easier. For example, finding "starting july onwards" would be just Month>=7.