[Resolved] Total Sum

[Ivains]

Hi Everyone,
I am trying to get the the sum of a db. The db list every time some signs up for a class. I would like to group it by lname, fname to give me the actual total of people that signed up (because some signed up for 2 or more classes - I just want to count them once).

Here is what I been playing with for 3 days now and I keep getting a count of '0'.

Any input would be gratefully appreciated.

---

$total_result = mysql_query("SELECT fname,SUM(lname) AS number FROM signup GROUP BY lname,fname");

while ($row = mysql_fetch_array($total_result)) {
$lname = $row['lname'];
$fname = $row['fname'];
$number = $row['number'];
}

echo "$number";

---

Thanks

[paulnaj]

SUM is for numerical fields ... that's your problem. COUNT is what you need, and if the simplest SQL to get the 'total' TOTAL (and not group totals as well) try ...

$total_result = mysql_query("SELECT COUNT(*) AS number FROM signup");

if(mysql_num_rows($total_result)){
$number = mysql_result($total_result , 0, 0);
}

echo "$number";

[Ivains]

Thanks, but this gives me the total number of records which is 119. In the db some individuals signed up for more than 2 classes, I trying to figure out the total number of individuals which should be 50.

When I add the GROUP BY:
SELECT COUNT(*) AS number FROM signup GROUP BY lname,fname

It gives me the number 3, which is how many classes the first individual sign up for. I want to count the number of rows that this statement returned and not the number that particular row.

Example it returned:

3
1
2
2
1

I want to be able to display the total number of rows which is "5".

This will give me the total number of students we served.

Any ideas? I might be going about this the wrong way???

Thanks

[diego25]

I'd structure your database this way, with at least 3 tables:

1. Student details
   Fields: StudentID, fname, lname, Age, etc

2. Course details
   Fields: CourseID, hours, etc

3. Registration details
   Fields: StudentID, CourseID, Paid, etc

Then, to get the students, you just COUNT(StudentID) on the student details table.

Diego

[Ivains]

I figure it out...

I was thinking wrong, but after that first cup of caffine...

This is what I did:

$total_result = mysql_query("SELECT * FROM signup GROUP BY lname,fname");

$row = mysql_fetch_array($total_result);

echo count($row);

[diego25]

That will count the number of columns, not the number of rows....

But, well, if you're sure it works...it works.

Diego