Example of my record :

year  quanitity name
2012     10     john
2012     20     mark
2013     30     david
2013     40     alex
2014     50     stacy



while (!$report->EOF){
        if(is_null($year) || $year <> $report->fields['year']) {
        $year = $report->fields['year'];
        ?>
        <tr><td align="center" colspan="2" >Year : </td><td><?=$year ?></td></tr>
        <?
            }
        ?>    
        <tr><td align="center" colspan="2" >Quantity : </td><td><?=$report->fields['quanitity'] ?></td></tr>
        <?
        $report->MoveNext();                          
        }

How to do so the result goes like this

year : 2012
name : john quantity : 10
name : mark quantity : 20

Total : 30

year : 2013
name : david quantity : 30
name : alex quantity : 40
Total : 70

Thanks in advance

Member Avatar for LastMitch

Grouping using while loop

I assume the code you provide works and when it echo out the info the results look like this:

year quanitity name
2012    10     john
2012    20     mark

You want the output to look like this:

year : 2012
name : john quantity : 10
name : mark quantity : 20

Am I correct?

the output look like this

year : 2012
name : john quantity : 10
name : mark quantity : 20

i just want add 'Total' for each year.

Member Avatar for LastMitch

There's a couple of ways you can do this:

$query = mysql_query("SELECT quanitity FROM table");
$sum = 0;

while($row = mysql_fetch_array($query)) {
$sum += $row['quanitity'];
}

echo $sum;

Add echo $sum; to your code above and it will echo the sum of the numbers

or you can try used SUM() function:

$query = "SELECT SUM(quanitity) FROM TABLE";
list($sum) = mysql_fetch_row($query);

<?php echo $sum; ?>

Thanks for your answer.. but your answer will sum all year..
I just want total for each year..

i still didnt get the correct answer

$query = "SELECT SUM(quanitity) FROM `tablename` GROUP BY `year`";

Backquotes might also be important if you use names that are same as mysql keywords.

commented: Good Answer +11

To get sum along with year use following query.

$query = "SELECT DISTINCT (
`year`), SUM(quanitity) FROM `tablename` GROUP BY `year`";
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.