Adding Values

[helpmeplease2]

How would I add up all the values in a column? They are amounts like 10.56 and 3.91, etc.

[LazyJones]

[!--quoteo(post=333293:date=Jan 4 2006, 04:41 PM:name=helpmeplease2)--][div class=\'quotetop\']QUOTE(helpmeplease2 @ Jan 4 2006, 04:41 PM) 333293[/snapback][/div][div class=\'quotemain\'][!--quotec--]

How would I add up all the values in a column? They are amounts like 10.56 and 3.91, etc.

 

SELECT SUM(column_name) FROM ...

[helpmeplease2]

Thank you! I have one more question. How would I add up only the values in a column which have an asterik (*) in the same row in a differnt column? Its hard to explain, so i'll try to show an example:

 

Bob - * - 10.91

Linda - - 1.31

Sue - * - 5.10

 

As you see bob and sue have asteriks and therefore the total would be 16.07, but it skips the ones without asteriks. Same question for how to find the sum of all the values without asteriks.

[LazyJones]

You have a column that has value asterix, hu? (or was that just an example?)

 

with *

SELECT SUM(column_name) FROM ... WHERE asterixcolumn = '*'

 

without *

SELECT SUM(column_name) FROM ... WHERE asterixcolumn <> '*'

 

[helpmeplease2]

Yes, I have a column with that has an * as the value. I got that working, but now I'm trying to line up all the numbers so they are right justified. The only way I could think of doing this and still having a differnt column being left justified was to use this code. But when trying to access the page it just keeps loading forever and nothing ever comes up. What is it that I am doing wrong?

 

$results=mysql_query("SELECT ID FROM customers ORDER BY id ASC");

$row=mysql_fetch_assoc($results);

echo "<table class=\"sortable\" border=\"0\" cellspacing=\"1\" cellpadding=\"1\" bgcolor=\"#dbe0e5\" width=\"700\"><tr>";

foreach(array_keys($row) as $coltitle){

echo "<th class=\"plan4\">$coltitle</th>";

}

 

$results=mysql_query("SELECT Name FROM customers ORDER BY id ASC");

$row=mysql_fetch_assoc($results);

foreach(array_keys($row) as $coltitle){

echo "<th class=\"plan3\">$coltitle</th>";

}

 

$results=mysql_query("SELECT R,Gross,Cash,Accrual FROM customers ORDER BY id ASC");

$row=mysql_fetch_assoc($results);

foreach(array_keys($row) as $coltitle){

echo "<th class=\"plan4\">$coltitle</th>";

}

echo "</tr>";

 

 

 

$results=mysql_query("SELECT ID FROM customers ORDER BY id ASC");

while($row=mysql_fetch_assoc($results)){

echo "<tr>";

foreach($row as $colval){

echo "<td class=\"plan4\">$colval</td>";

}

 

$results=mysql_query("SELECT Name FROM customers ORDER BY id ASC");

$row=mysql_fetch_assoc($results);

foreach($row as $colval){

echo "<td class=\"plan3\">$colval</td>";

}

 

$results=mysql_query("SELECT R,Gross,Cash,Accrual FROM customers ORDER BY id ASC");

$row=mysql_fetch_assoc($results);

foreach($row as $colval){

echo "<td class=\"plan4\">$colval</td>";

}

echo "</tr>";

[LazyJones]

looks like copy/paste monster has striked again

 

while($row=mysql_fetch_assoc($results)){

 

EDIT: maybe you should think again about the query structures. Why are you fetching all the column names and values separetly?

Here's how you can display all the column names:

 

echo "<table class=\"sortable\" border=\"0\" cellspacing=\"1\" cellpadding=\"1\" bgcolor=\"#dbe0e5\" width=\"700\">";
$results=mysql_query("SELECT * FROM customers ORDER BY id ASC");
echo '<tr>'
$row=mysql_fetch_assoc($results);
foreach(array_keys($row) as $coltitle){
    echo "<th class=\"plan4\">$coltitle</th>";
}
echo '<tr>';

 

and the values:

$results=mysql_query("SELECT * FROM customers ORDER BY id ASC");
echo '<tr>'
$row=mysql_fetch_array($results);
    echo "<td class=\"plan4\">".row['ID'].</td>";
    echo "<td class=\"plan3\">".row['Name]."</td>";
    echo "<td class=\"plan4\">".row['R']."</td>";
    ...
}
echo '</tr>';
echo '</table>';

[helpmeplease2]

Thank you, you've been so much help. I just have 2 more questions:

 

1. Now its only displaying the first row in the table. I had it working before showing all the rows, whats wrong?

 

2. When adding the SUM of all values in a column it displays a number such as 1931.7, how can I make it so this number says 1931.70?

[LazyJones]

[!--quoteo(post=333603:date=Jan 5 2006, 01:22 PM:name=helpmeplease2)--][div class=\'quotetop\']QUOTE(helpmeplease2 @ Jan 5 2006, 01:22 PM) 333603[/snapback][/div][div class=\'quotemain\'][!--quotec--]

Thank you, you've been so much help. I just have 2 more questions:

 

1. Now its only displaying the first row in the table. I had it working before showing all the rows, whats wrong?

 

2. When adding the SUM of all values in a column it displays a number such as 1931.7, how can I make it so this number says 1931.70?

 

1. It was because you only fetched the first row (see my edited reply)

 

2. number_format()

[fenway]

1. As LazyJones indicated, you're missing a while() loop to interate through the result set.

 

2. You can also use MySQL's TRUNCATE(SUM(your_column),2) function to accomplish this task directly.

 

Hope that helps.

[helpmeplease2]

Instead of grabbing the data from the database it is just displaying '.row['ID'].' '.row['Name'].' '.row['R'].' '.row['Gross'].... etc

 

Heres what I'm using, I tried a few things but can't find the problem:

 

echo "<table class=\"sortable\" border=\"0\" cellspacing=\"1\" cellpadding=\"1\" bgcolor=\"#dbe0e5\" width=\"700\">";

$results=mysql_query("SELECT * FROM customers ORDER BY id ASC");

echo '<tr>';

$row=mysql_fetch_assoc($results);

foreach(array_keys($row) as $coltitle){

echo "<th class=\"plan4\">$coltitle</th>";

}

echo '<tr>';

 

$results=mysql_query("SELECT * FROM customers ORDER BY id ASC");

echo '<tr>';

$row=mysql_fetch_array($results);

echo "<td class=\"plan4\">'.row['ID'].'</td>";

echo "<td class=\"plan3\">'.row['Name'].'</td>";

echo "<td class=\"plan4\">'.row['R'].'</td>";

echo "<td class=\"plan4\">'.row['Gross'].'</td>";

echo "<td class=\"plan4\">'.row['Cash'].'</td>";

echo "<td class=\"plan4\">'.row['Accrual'].'</td>";

 

echo '</tr>';

[LazyJones]

Sorry, I missed a loop in my code

 

echo '<tr>';
while ($row=mysql_fetch_array($results)) {
    echo "<td class=\"plan4\">'.row['ID'].'</td>";
    echo "<td class=\"plan3\">'.row['Name'].'</td>";
    echo "<td class=\"plan4\">'.row['R'].'</td>";
    echo "<td class=\"plan4\">'.row['Gross'].'</td>";
    echo "<td class=\"plan4\">'.row['Cash'].'</td>";
    echo "<td class=\"plan4\">'.row['Accrual'].'</td>";
}
echo '</tr>';

[fenway]

Dollar signs, anyone?

[helpmeplease2]

Its still displaying '.row['ID'].' '.row['Name'].' '.row['R'].' '.row['Gross']... etc, but now it repeats this like 50 times so it scrolls sideways a long ways. Whats wrong?

[LazyJones]

omg

 

$row['Name']

 

Sure you got the basics?

 

And place the <tr>'s inside the loop.

[helpmeplease2]

I don't know all the basics yet. I added the $ signs and moved the <tr>'s. Its now showing the data correctly, except the first row isn't showing up. I love you LazyJones

[fenway]

Sounds like you still have a call to mysql_fetch() before the while loop -- in essense, you're throwing out the first row by accident.

[helpmeplease2]

Do you know why its not showing the first row? Also I can't get the TRUNCATE to work. I want the numbers to align, its dropping the last 0 when I want the 0 to be there.

[fenway]

I told you why -- you're incrementing the row pointer by accident somewhere in your code. If you see mysql_fetch() anywhere other than in the while loop, that's the problem. Second, TRUNCATE() does work... try running "TRUNCATE(1.2,2)".. you should get 1.20.

[helpmeplease2]

I'm sorry, I didn't see this second page. This is what I have before the while:

 

echo "<table class=\"sortable\" border=\"0\" cellspacing=\"1\" cellpadding=\"1\" bgcolor=\"#AAAAAA\" width=\"700\">";

$results=mysql_query("SELECT * FROM customers ORDER BY id ASC");

echo '<tr bgcolor="#CCDDEE">';

$row=mysql_fetch_assoc($results);

foreach(array_keys($row) as $coltitle){

echo "<th class=\"plan3\" bgcolor=\"#CCDDEE\">$coltitle</th>";

}

 

 

How would I fix this? Maybe if I make the column titles in a different table? I used TRUNCATE and it turned 1.4 into 1.39, but I want it to change into 1.40. I just want there to always be 2 decimal places.

[LazyJones]

That's for fetching the column names. They are all right, aren't they? You have an extra mysql_fetch_xxx() in the code where you fetch the values.

[fenway]

This is starting to get a little ridiculous -- you're using the column names from the first record of the result set, but throwing out the actual data. That is _exactly_ the call to mysql_assoc() that we're talking about. You're also missing the close TR for the heading row. My suggestion: simply run that foreach again, and spit of the _values_ for that row, and then start the while loop. Second, WRT TRUNCATE(), it _does_ give you two decimal places, as promised. Maybe you want the ROUND() function instead.

 

BTW, you've asked about 10 separate questions in a single thread -- perhaps you should take the time to try and figure some of this out on your own time first to see how much you can get resolved in advance. All of the advice & suggestions you need to complete your task have been laid out already in this thread. Good luck.

[helpmeplease2]

I'm sorry if I've asked too many questions, I really new to PHP. I placed:

 

$results=mysql_query("SELECT * FROM customers ORDER BY id ASC");

 

in front of the while and now the first result is showing up!

 

I'm not sure if you understood what I was trying to say with the decimal points, but the meaning of truncate is the opposite of what I'm trying to do. Truncate made the correct amount of decimals but they weren't correct.

 

I had it work for some numbers such as 31.9, but its also changing into 5.42 into 5.41. Whats it doing??? It should still say 5.42.

[fenway]

It's not that you're asking too many questions -- ask as many as you want, that's what this forum is all about! It's just that when it's all in one thread, it's often hard to follow when the "question" changes.

 

TRUNCATE() does exactly that, with no rounding. If you're getting 5.42 becoming 5.41, then that means the "underlying value" is probably 5.419, and whatever is giving you 5.42 is simply rounding. When you truncate, you just chop the number of N decimal places, so you'll get 5.41. Does that make sense?