SUM() Question

[NSH]

Yet another sum question...
I have scoured the net and forums for a way to implement what I need, however either I am an idiot or it hasn't been discussed. I choose idiot 🙂

Problem:

I have about 10 fields that each have data in them(rows).

I currently have the contents being dumped by using SELECT * from turns where date=CURDATE()

Combine that with a if,while,else statement works fine for dumping the data(which is what I want)

I also want to add each field's row values individually(except date of course)

I have been able to simply use the following query to get the sum results:

SELECT SUM(servreq),SUM(requestid) from turns

I am then able to get the added values correctly for both of those fields.
However, when attempting to add the to dump all data as well i have issues. I have used:
SELECT ,SUM(servreq),SUM(requestid) from turns GROUP BY date
I have also used other fields in the group by section.
That gives me grouped results for each row that dumps in the while statement.

Basically what I need is a way to dump all my data to the page and get the sum of each field to display at the bottom.

example:

Date | Field | Field | Field
2002 | 1 | 3 | 7
2002 | 1 | 3 | 7
2002 | 1 | 3 | 7
2002 | 1 | 3 | 7
SUM | 4 | 12 | 28

I can't seem to get SUM(), * and group by to work for me.

This also may have been a terrible description as I am at work and have a headache.

Thanks to anyone who reads my drivel

[reidme]

I had a similar obstacle when trying to select all the Articles from a db, and then cross reference a Categories table to find out the total articles in each category.
In the end I had to put in a seperate db query, because it was the only way i could get both sets of data, they wouldnt work in a single query (theres loads of irelevant code in there btw, sorry):

<? //DISPLAY CATS
$result = mysql_query("SELECT * FROM Cats order by ID");
if (!$result) {echo "<p>Gadzooks!! mysql_error ()</p>"; exit();}
$AltRow1 = new AltRow;
$AltRow1->SetClasses('cat-list-row1','cat-list-row2');
while ($row = mysql_fetch_array($result)) {
$CatID		=	$row["0"];
$CatName 	=	$row["1"];
$CatInfo	=	$row["2"];
$query2 = mysql_query("SELECT count(*) AS sum FROM Articles WHERE Cat = $CatID");
     while ($results2 = mysql_fetch_array($query2)){
?>
<table width="100%" class="<? $AltRow1->getClass(); $AltRow1->increment();?>" border="0" cellspacing="0" cellpadding="2">
<tr><td width="50"><a href="admin.php?udcid=<? echo $CatID ;?>"><? echo $CatID;?></a></td>
<td width="150"><a href="admin.php?view_cat_id=<? echo $CatID;?>"><? echo $CatName;?></a></td>
<td><? echo $CatInfo;?></td>
<td width="30"><? echo "$results2[sum]";?></td></tr></table>

<? }
}?>

I made the second query come in before the tables were made for the displaying of the results.
Hope this helps.

[NSH]

I was trying to avoid multiple queries for the simple fact I can never get them to work :p

I shall give it another go!

1 other question

if in my second query I have multiple fields

SELECT SUM(blah), SUM(blah2) FROM blah

When I go to echo them(after the while command)

do I just do

echo $result2['blah'];
echo $result2['blah2'];

btw thank you very much for your help!

[reidme]

Furst I should have mentioned that the above solution was given to me by someone on these boards.

Do you have access to the mysqll command line client? If so you can run the second query in there and see what the resultant variable name will be. I'm not sure what the associated array pointer will be. The only way is to try it out and if you cant get it change the query to 'SUM(blah) as col1, SUM(blah2) as col2.
If you use mysql_fetch_assoc rather than mysql_fetch_array, you get an associated array that is easier for noobs like me to deal with, and you will be able to reference each value with $result["col1"] $result["col2"] etc...
You are welcome btw 😃

[NSH]

sigh, for some reason i can't get it to work correctly and display the sum.
I have tried every variation of the code i can think of.
I shall keep trying.
ANyone else have any suggestions? ( thanks again)

[reidme]

Can you paste your code in here?

[NSH]

$query2 = "SELECT SUM(servreq) FROM turns where date = CURDATE()";

$result2 = mysql_query($query2);

while ($s = mysql_fetch_array($result2)){
$servsum = $s[servreq];
 echo $servsum;
}

that is one variation of it
basically i emulated what you did at first, then i copied my code from the query that is dumping results....
still nothing

[NSH]

well i got it

used

$servsum = $s[0];

it worked.

Thanks for all your help!!!

I appreciate it!!!!!

[reidme]

while ($row = mysql_fetch_array($result)) {
$CatID        =    $row["0"];
$CatName     =    $row["1"];
$CatInfo    =    $row["2"];
$query2 = mysql_query("SELECT count(*) AS sum FROM Articles WHERE Cat = $CatID");
     while ($results2 = mysql_fetch_array($query2)){
?>

I was sending the second query once every loop. Using the value given from the first query: $CatID ($row["0"]) as a part of the second query:
("SELECT count(*) AS sum FROM Articles WHERE Cat = $CatID");

Looking again at your need I don't think it will help you, because you are getting the sum() not the count().
This is beyond me I'm afraid, I'm sure someone will help though.
What I do know is that to use a while loop to get the results your query should look like this:
"Select sum($col_name) from turns where date = CURDATE()".
How you get the column name into the $col_name variable dynamically I don't know. Maybe you can reference them via there order of appearance? Or grab them all in one go from a DESCRIBE type statement and put them in an array ?
Interested in your progress 😃

[NSH]

thanks for the update, guess you were typing when I posted last 🙂

I figured it out, just used the row #

I then proceed to add ALL of my columns i wanted to sum:

$sumquery = "
SELECT SUM(servreq), 
SUM(ftpmove),
SUM(com),
SUM(comchk),
SUM(virtualdir),
SUM(scriptmod),
SUM(sitescope),
SUM(oracle),
SUM(edocs),
SUM(actuate),
SUM(iwscript) FROM turns where date = CURDATE()";

$sumresult = mysql_query($sumquery);

while ($s = mysql_fetch_array($sumresult)){
$servreqsum = $s[0];
$ftpmovesum = $s[1];
$comsum = $s[2];
$comchksum = $s[3];
$virtualdirsum = $s[4];
$scriptmodsum = $s[5];
$sitescopesum = $s[6];
$oraclesum = $s[7];
$edocssum = $s[8];
$actuatesum = $s[9];
$iwscriptsum = $s[10]; 

}

//I Then echo the results

echo "
<tr bgcolor=#CCCCCC id=\"ignore\"><td><b>TOTAL</td><TD></TD>
<td width=\"10%\"><font size=\"2\">$servreqsum</td>
<td width=\"10%\"><font size=\"2\">$ftpmovesum</td>
<td width=\"10%\"><font size=\"2\">$comsum</td>
<td width=\"10%\"><font size=\"2\">$comchksum</td>
<td width=\"10%\"><font size=\"2\">$virtualdirsum</td>
<td width=\"10%\"><font size=\"2\">$scriptmodsum</td>
<td width=\"10%\"><font size=\"2\">$sitescopesum</td>
<td width=\"10%\"><font size=\"2\">$oraclesum</td>
<td width=\"10%\"><font size=\"2\">$edocssum</td>
<td width=\"10%\"><font size=\"2\">$actuatesum</td>
<td width=\"10%\"><font size=\"2\">$iwscriptsum</td></tr></table>";

Works perfectly.

Thanks for your help!!!

I organized the query like I did for ease of readability...just an fyi
I guess I didn't have to set a variable for each row, I just find it easier when echoing it...so i know what i'm doing 🙂

[reidme]

Nice one 😃
Glad you got it going, did you punch the air -
"GIT IN THERE MY SAN!!!!"
?
lol.