CODEWITHSUNDEEP
mysqlsqlsum
kuroALICE
kuroALICE

Reputation: 11

Sum of Selected data in php mysql

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.

Upvotes: 1

Views: 111

Answers (3)

KennethLJJ
KennethLJJ

Reputation: 157

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.

Upvotes: 0

NULL
NULL

Reputation: 1858

I think you have to use GROUP BY student_id.

Upvotes: 2

Peter
Peter

Reputation: 793

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.

Upvotes: 2

Related Questions