Sum of Selected data in php mysql

Question

I've tried several codes for me to attain the results needed. But it still gives me the wrong results. The results that it gave me is the most recent entry and not the sum of my selected data.

CREATE TABLE `assessments`
(`af_id` int(11) NOT NULL AUTO_INCREMENT,
 `assess_date` varchar(100) NOT NULL,
 `student_id` int(11) NOT NULL,
 `total_fee` varchar(120) NOT NULL,
 PRIMARY KEY (`af_id`)
) ENGINE=InnoDB AUTO_INCREMENT=83 DEFAULT CHARSET=latin1

I'm not the one who made the table. So, I'm also having difficult with it.

<?php
$row2=mysql_fetch_assoc(mysql_query("Select student_id,SUM(total_fee) AS total_fee from assessments where student_id='$student_id'"));
$assess_fee = 0;
$assess_fee = $row2['total_fee'];
echo "<tr>";
echo    "<td width='13%' style='font-size:12px'><b>Entries: $entries</b></td>";
echo    "<td width='52%' style='text-align: right'><b>TOTAL ASSESSED FEES</b></td>";
echo    "<td width='5%' style='text-align: right'><b>Php</b></td>";
echo    "<td width='20%' style='text-align: right'><b>".number_format($assess_fee,2)."</b></td>";
echo "</tr>";
?>

The primary key is different than the student_id because there are multiple entry of it in the table. I need to have the total fees for the selected IDs.

Peter · Accepted Answer · 2014-07-07 02:00:23Z

2

You need to group the student ID's together. Without grouping the student id's together it does not know what it is meant to be adding together when doing sum. Change the SQL to:

 Select student_id,SUM(total_fee) AS total_fee from assessments where student_id='$student_id' GROUP BY student_id

This should resolve your issue.

answered Jul 7, 2014 at 2:00

Peter

7931 gold badge8 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kuroALICE Over a year ago

yes tried it. but it still gives me the wrong results. it only shows the recent entry instead of the sum.

NULL · Accepted Answer · 2014-07-07 02:04:55Z

2

I think you have to use GROUP BY student_id.

edited Jul 7, 2014 at 2:04

answered Jul 7, 2014 at 1:59

NULL

1,8481 gold badge21 silver badges23 bronze badges

3 Comments

kuroALICE Over a year ago

I've tried that already, but it still gives me the wrong results.

kuroALICE Over a year ago

sorry but how do I do that? I've never tried that before. just new here.

NULL Over a year ago

thanks for the schema, ur total_fee is varchar, u couldn't sum up characters, total_fee should have been int or double.

KennethLJJ · Accepted Answer · 2014-07-07 03:02:00Z

0

If I am not wrong, you should not be using "mysql_fetch_assoc". If you look at the "Return Values" section from http://www.php.net/manual/en/function.mysql-fetch-assoc.php It states:

If two or more columns of the result have the same field names, the last column will take precedence. To access the other column(s) of the same name, you either need to access the result with numeric indices by using mysql_fetch_row() or add alias names.

You might want to try getting the number of rows that your query has return, loop through and use mysql_fetch_row() to get each individual "total_fee" and sum them up.

answered Jul 7, 2014 at 3:02

KennethLJJ

1573 silver badges12 bronze badges

Collectives™ on Stack Overflow

Sum of Selected data in php mysql

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related