Sub-Totals and Totals
Just curious how everyone does this. Say you have a column that you want to have sub-totals and/or totals print at the bottom of the page totalling the sum of various columns. What do you think the best way to get the totals? I usually do it using MySQL, using the sum function, like so :SELECT sum(fieldname) as fieldname_total
Any ideas?
Mark Irving
I have a mind like a steel trap; it is rusty and illegal in 47 states
Suzanne posted this at 16:18 — 3rd June 2003.
She has: 5,507 posts
Joined: Feb 2000
Interesting. I haven't done anything that requires tabulating with a database, so I'm not much help opinion-wise (aren't you relieved!) but I'm curious about whether it'd be better to have the database do the work or the application (perl/php/asp)?
Mark Hensler posted this at 16:27 — 3rd June 2003.
He has: 4,048 posts
Joined: Aug 2000
I like to do as much work in mySQL as possible.
SELECT sum(countfield) AS fieldname_subtotal FROM tablename GROUP BY typefield
This would give subtotals for various `typefield`, then suming those (in PHP) would give a grand total.
Mark Hensler
If there is no answer on Google, then there is no question.
mairving posted this at 16:43 — 3rd June 2003.
They have: 2,256 posts
Joined: Feb 2001
I usually prefer to do in SQL, depending on the query times. I wrote it as a function in a class like so:
<?phpfunction count($fieldname) {
$db_sql = new db_sql;
$count = $fieldname.\"_count\";
$q = \"SELECT sum($fieldname) as $count \";
$q .= \"FROM tablename \";
$q .= \"WHERE $fieldname >= '1' \";
$db_sql->query($q);
$db_sql->next_record();
return $count;
}
?>
So that I just have to call it via:
<?php$class->count(\"fieldname\");
?>
Mark Irving
I have a mind like a steel trap; it is rusty and illegal in 47 states
Suzanne posted this at 16:54 — 3rd June 2003.
She has: 5,507 posts
Joined: Feb 2000
What's the advantage to using MySQL (or any other db) over using scripting?
mairving posted this at 17:15 — 3rd June 2003.
They have: 2,256 posts
Joined: Feb 2001
The advantage is usually that there is less code to write which, of course, reduces debugging time, etc. Speed is better usually as long as it is not a complex query.
Mark Irving
I have a mind like a steel trap; it is rusty and illegal in 47 states
Mark Hensler posted this at 02:29 — 4th June 2003.
He has: 4,048 posts
Joined: Aug 2000
You're creating an object within a function? You should probably make the variable static, or pass the object as an argument to the function (or just destry the object before returning from the function).
Mark Hensler
If there is no answer on Google, then there is no question.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.