22 replies to this topic

• Members
• 87 posts

Posted 04 January 2006 - 09:41 PM

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

### #2 LazyJones

LazyJones
• Members
• 78 posts

Posted 04 January 2006 - 09:46 PM

[!--quoteo(post=333293:date=Jan 4 2006, 04:41 PM:name=helpmeplease2)--][div class=\'quotetop\']QUOTE(helpmeplease2 @ Jan 4 2006, 04:41 PM) [/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.
[/quote]

SELECT SUM(column_name) FROM ...

• Members
• 87 posts

Posted 04 January 2006 - 11:55 PM

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.

### #4 LazyJones

LazyJones
• Members
• 78 posts

Posted 04 January 2006 - 11:59 PM

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 <> '*'

• Members
• 87 posts

Posted 05 January 2006 - 05:56 PM

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>";

### #6 LazyJones

LazyJones
• Members
• 78 posts

Posted 05 January 2006 - 06:05 PM

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>';
```

• Members
• 87 posts

Posted 05 January 2006 - 06:22 PM

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?

### #8 LazyJones

LazyJones
• Members
• 78 posts

Posted 05 January 2006 - 06:28 PM

[!--quoteo(post=333603:date=Jan 5 2006, 01:22 PM:name=helpmeplease2)--][div class=\'quotetop\']QUOTE(helpmeplease2 @ Jan 5 2006, 01:22 PM) [/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?
[/quote]

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

2. number_format()

### #9 fenway

fenway
• Staff Alumni
• MySQL Si-Fu / PHP Resident Alien
• 16,199 posts
• LocationToronto, ON

Posted 05 January 2006 - 07:25 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

• Members
• 87 posts

Posted 05 January 2006 - 07:43 PM

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>';

### #11 LazyJones

LazyJones
• Members
• 78 posts

Posted 05 January 2006 - 07:50 PM

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>';
```

### #12 fenway

fenway
• Staff Alumni
• MySQL Si-Fu / PHP Resident Alien
• 16,199 posts
• LocationToronto, ON

Posted 05 January 2006 - 08:10 PM

Dollar signs, anyone?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

• Members
• 87 posts

Posted 05 January 2006 - 08:11 PM

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?

### #14 LazyJones

LazyJones
• Members
• 78 posts

Posted 05 January 2006 - 08:36 PM

omg

\$row['Name']

Sure you got the basics?

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

• Members
• 87 posts

Posted 05 January 2006 - 10:24 PM

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

### #16 fenway

fenway
• Staff Alumni
• MySQL Si-Fu / PHP Resident Alien
• 16,199 posts
• LocationToronto, ON

Posted 05 January 2006 - 10:29 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

• Members
• 87 posts

Posted 06 January 2006 - 01:54 AM

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.

### #18 fenway

fenway
• Staff Alumni
• MySQL Si-Fu / PHP Resident Alien
• 16,199 posts
• LocationToronto, ON

Posted 06 January 2006 - 03:21 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

• Members
• 87 posts

Posted 06 January 2006 - 03:37 AM

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
• Members