How to get average value based on user selection?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
xhkmx
Forum Newbie
Posts: 1
Joined: Wed Nov 19, 2014 1:43 am

How to get average value based on user selection?

Post 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 } ?>
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

Post 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;
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

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

Post 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.
(#10850)
Post Reply