PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Wed Dec 12, 2018 12:58 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 3 posts ] 
Author Message
PostPosted: Wed Nov 19, 2014 2:50 am 
Offline
Forum Newbie

Joined: Wed Nov 19, 2014 2:43 am
Posts: 1
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:
Syntax: [ Download ] [ Hide ]
$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:

Syntax: [ Download ] [ Hide ]
<?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 } ?>


Top
 Profile  
 
PostPosted: Wed Nov 19, 2014 1:57 pm 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6617
Location: WA, USA
You can build an array of all the values, starting with July's,
Syntax: [ Download ] [ Hide ]
$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
Syntax: [ Download ] [ Hide ]
$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
Syntax: [ Download ] [ Hide ]
$subset = array_slice($totals, 0, $count);

and getting the average is just a matter of some quick math.
Syntax: [ Download ] [ Hide ]
$average = array_sum($subset) / $count;


Top
 Profile  
 
PostPosted: Wed Nov 19, 2014 3:11 pm 
Offline
Site Administrator
User avatar

Joined: Wed Aug 25, 2004 7:54 pm
Posts: 13583
Location: New York, NY, US
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)


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 3 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: Majestic-12 [Bot] and 7 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group