Little help with SUM

trekky28

I've come to a dead end with this.
A table like this:
Item1 | Item2 | Item3
8 | 4 | 2
7 | 4 | 8
5 | 3 | 9

How do I form SELECT statement to get all 3 columns SUM displayed like:

Item nr | Total (sorted desc)

Item1 | 20
Item3 | 19
Item2 | 11

There is no specific column to group by..

devinemke

you could do...

SELECT SUM(Item1), SUM(Item2), SUM(Item3) FROM table

...which would return all 3 sums in a single row which can easily then be spooled into an array and sorted/displayed however you want. or you can do each SUM() as a separate query and have them returned togther using the UNION() syntax which would return 3 separate rows.

trekky28

This is what I tried. Gives no error but doesn't display any results either. What am I missing here?

<?

$grab_votes = "SELECT SUM(Item1), SUM(Item2), SUM(Item3) FROM votes";
$result = mysql_query($grab_votes);
sql_query("$result", "$error_messages[04]");

$itemVotes = array("Item1","Item2","Item3"); 
$itemNames = array("Item1","Item2","Item3"); 

$num_rows = mysql_num_rows($result);

IF (mysql_num_rows($result) == 0)
{
echo ("<SPAN CLASS='error_text'>
No entries exist.
</SPAN>");
}


ELSE
{
?>

<DIV ALIGN='CENTER'>
<TABLE BORDER=0 COLS=2 WIDTH=300>
<TR>
<TD CLASS='table_header' ALIGN='CENTER'>
<B>Item</B>
</TD>
<TD CLASS='table_header' ALIGN='CENTER'>
<B>Votes</B>
</TD></tr>


<?


$row_counter = 0;

while ($row = mysql_fetch_array($result))
{
++$row_counter;
$itemNames = $row["item"];
$itemVotes = $row["votes"];
rounded ($row_counter);
?>



<TR>

<TD ALIGN='CENTER' CLASS='<? require ("votes_config.php"); echo ($row_background); ?>'>
<? echo ($item); ?>
</TD>

<TD ALIGN='CENTER' CLASS='<? echo ($row_background); ?>'>
<? echo ($votes); ?>
</TD>

</TR>

qadeer_ahmad

Hi,

Just remove this line and check the result
sql_query("$result", "$error_messages[04]");

trekky28

Nope, same result. Nothing is displayed apart from table header..

qadeer_ahmad

while ($row = mysql_fetch_array($result))
{

before this again run mysql_query because you already get data from this $result on num method so before this again write this
$result = mysql_query($grab_votes);
it will give you data, hopefully.

trekky28

Nope, I tried that too before. Even removed the num check. It doesn't display data.
I think something's wrong with the overall array display..

qadeer_ahmad

Ohh ic one thing i chech
IF (mysql_num_rows($result) == 0)
php is casesentisive in if so its should be
if (mysql_num_rows($result) == 0)
also
ELSE will be else

perform all changes above which i tell you before this post and check again.

trekky28

Nope, didn't work either...
I can get the item values displayed each but I want them to be sorted desc..else¨
I would just write <? echo ($Item1); ?>
and so on, on each row.. Same with item name which is the same as column name.

qadeer_ahmad

and
<? echo ($item); ?>
where you get value for $item thier will be
<? echo ($itemNames); ?>
and
<? echo ($votes); ?>

will replace with
<? echo ($itemVotes); ?>

qadeer_ahmad

can you tell me structure of votes table?

qadeer_ahmad

ok i arrange a script that will help you

<? 

$grab_votes = "SELECT SUM(Item1) as item1, SUM(Item2) as item2, SUM(Item3) as item3 FROM votes"; 
$result = mysql_query($grab_votes); 

$itemVotes = array("Item1","Item2","Item3"); 
$itemNames = array("Item1","Item2","Item3"); 



if (mysql_num_rows($result) == 0) 
{ 
echo ("<SPAN CLASS='error_text'> 
No entries exist. 
</SPAN>"); 
} 
else 
{ 
?> 

<DIV ALIGN='CENTER'> 
<TABLE BORDER=0 COLS=2 WIDTH=300> 
<TR> 
<TD CLASS='table_header' ALIGN='CENTER'> 
<B>Item</B> 
</TD> 
<TD CLASS='table_header' ALIGN='CENTER'> 
<B>Votes</B> 
</TD></tr> 


<? 


$row_counter = 0; 
$result2 = mysql_query($grab_votes); 
while ($row = mysql_fetch_array($result2)) 
{ 
++$row_counter; 
$item1 = $row["item1"]; 
$item2 = $row["item2"];
$item3 = $row["item3"];

rounded ($row_counter); 
?> 



<TR> 

<TD ALIGN='CENTER' CLASS='<? require ("votes_config.php"); echo ($row_background); ?>'> 
<? echo ($item1); ?> 
</TD> 

<TD ALIGN='CENTER' CLASS='<? echo ($row_background); ?>'> 
<? echo ($item2); ?> 
</TD> 

</TR>

it will be use to show sum if you want to show all records then you need new query before while statment like this

$grab_votes = "SELECT * FROM votes";
then
these value depend on your votes table structure
if thier is three field you want to get then you can also use like this
$item1 = $row[0];
$item2 = $row[1];
$item3 = $row[2];
or give field name exact as table fields name

i hope this will solve your problem
Best of Luck

Blulagoon

Try and see if this works for you:

$grab_votes = "SELECT SUM(Item1) AS vote1, SUM(Item2) AS vote2, SUM(Item3) AS vote3 FROM votes"; 
$result = mysql_query($grab_votes); 
$row = mysql_fetch_assoc($result);
$vote1 = $row['vote1'];  // and same for vote2 and vote3
echo $vote1;

This should get your SUM calculations into appropriate variables which you can then use as required elsewhere in the script. Always a good idea to echo out the variables right at the beginning so you know that you have the right values there, then delete that line of code later.

HTH - Blu

trekky28

Hi guyz.

The $row = mysql_fetch_assoc($result); does work.

However,

Table display should be

<TR>

<TD ALIGN='CENTER' CLASS='<? echo ($row_background); ?>'>
<? echo ($Item1); ?> // Itemname here (Item1)
</TD>

<TD ALIGN='CENTER' CLASS='<? echo ($row_background); ?>'>
<? echo ($Item1); ?> //Itemvalue here (Item1)
</TD>

</TR>
<TR>

<TD ALIGN='CENTER' CLASS='<? echo ($row_background); ?>'>
<? echo ($Item2); ?> // Itemname here (Item2)
</TD>

<TD ALIGN='CENTER' CLASS='<? echo ($row_background); ?>'>
<? echo ($Item2); ?> //Itemvalue here (Item2)
</TD>

</TR>
And so on.. Sorted bt Itemvalue desc.

Blulagoon

Once you've got the SUM values ($vote1 etc from my example) you could insert them into a temporary table along with the item name, then do a select on the temp table ordering the output by value desc.

Probably not the neatest way of doing it, but it should work just fine. The temp table should only exist for the duration of the mysql connection, however with temp tables I always use mysql_close($conn), which of course one always should use ........

HTH - Blu

trekky28

I've never done a temporary table. How does it work?

Houdini

They work the same as a regular table except when you are finished using it it is destroyed, you might want to search for tutorails on how to create and use them.

Blulagoon

I've been trying to find a web resource for you, will post one if I find one:

Broadly you use the same syntax as you would with MySQL's CREATE TABLE function:

$sql="CREATE TEMPORARY TABLE temptable (item_name VARCHAR(30), vote_score INT)";
$result=mysql_query($sql);

Then use INSERT INTO temptable(columns) values(values) as you would any table.

HTH - Blu

Blulagoon

If your site is busy and you are worried about a second user trying to create a temporay table with the same name as an existing one (unlikely but ....... ) you can do something like this:

<?PHP

$rand=mt_rand(1,1000);
$tablename="temptable".$rand;

$sql="CREATE TEMPORARY TABLE ".$tablename." (item_name varchar(20), vote_score int)";

echo $tablename;
echo "<br>";
echo $sql;
?>

trekky28

I'm not sure what I'm doing and it's not working...

Here's what I've done:

<?

$mysql_link = mysql_pconnect( "$DBhost", "$DBuser", "$DBpass") or die
("Cannot reach the server. Please send a mail to $admin_email.");
mysql_select_db( "$DBName") or die( "Cannot reach the database. Please send a mail to $admin_email.");


 $grab_votes = "SELECT SUM(al) AS sum_al, SUM(ad) AS sum_ad, SUM(am) AS sum_am FROM votes";
$result = mysql_query($grab_votes);
$row = mysql_fetch_assoc($result);
$sum_al = $row['sum_al']; 
$sum_ad = $row['sum_ad']; 
$sum_am = $row['sum_am'];  

$sql="CREATE TEMPORARY TABLE temptable (item VARCHAR(30), total INT)";


$insert_votes ="INSERT INTO temptable(item, total) VALUES('al', '$sum_al'),('ad', '$sum_ad'),('am', '$sum_am');



$grab_total = "SELECT SUM(total) as total, item FROM temptable GROUP BY item ORDER BY total DESC , item ASC";
$result4 = mysql_query($row,$sql,$insert_votes,$grab_total);
sql_query("$result4");
?>

<DIV ALIGN='CENTER'>
<TABLE BORDER=0 COLS=2 WIDTH=300>
<TR>
<TD CLASS='table_header' ALIGN='CENTER'>
<B>Item</B>
</TD>
<TD CLASS='table_header' ALIGN='CENTER'>
<B>Votes</B>
</TD></tr>


<?


$row_counter = 0;

while ($row = mysql_fetch_array($result4))
{
++$row_counter;
$item = $row["item"];
$total = $row["total"];
rounded ($row_counter);
?>



<TR>

<TD ALIGN='CENTER' CLASS='<? echo ($row_background); ?>'>
<? echo ($item); ?>
</TD>

<TD ALIGN='CENTER' CLASS='<? echo ($row_background); ?>'>
<? echo ($total); ?>
</TD>

</TR>

<?
}
?>