Tally the SUM of a column of a table ...

[xProteuSx]

I am trying to figure out how to tally the column in a table.  I have a table, and for each user there is a value in the 'PagesViewed' column.  I would like to add up the page views of all the users in the database.  How do I go about doing this?  If I use a 'FOR' loop this would take a lot of calculation.  Is there an easier way?  Thanks in advance.

[Northern Flame]

this is the best i can think of

 

<?php

 

$query = mysql_query("select * from table");

 

$x = 0;

 

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

$array[$x] = $row['PagesViewed'];

$x++;

}

 

echo array_sum($array);

 

?>

[pocobueno1388]

There is a much easier way.

 

SELECT SUM(PagesViewed) as total FROM tableName

[JustinK101]

Yeap, pocobueno is right:

 

$sql = "SELECT * FROM table";
$result = mysql_query($sql) or die(mysql_error());

$sql_total = "SELECT SUM(PagesViewed) AS total FROM table";
$result_total = mysql_query($sql_total) or die(mysql_error());
$row_total = mysql_fetch_object($result_total);

while($row = mysql_fetch_object($result)) {
   echo $row->title;
}

echo $row_total->total;

[xProteuSx]

Thanks for your input guys.  Northern Flame, I was looking to do something without a loop.  It figured that there would be a function which would tally a column.  God knows it would be a function used often.  So what pocobueno1388 was saying was along the lines of what I expected.  I have done the following, pocobueno:

 

$tallypageviews = mysql_query("SELECT SUM(users_pagesviewed) as total FROM users");
echo 'Pages Viewed By All Members: ';
echo $tallypageviews;  

 

However, the output is:

 

Pages Viewed By All Members: Resource id #14

 

I'm trying to figure this one out, but help would be appreciated.

 

[pocobueno1388]

You code should be:

 

<?php

$tallypageviews = mysql_query("SELECT SUM(users_pagesviewed) as total FROM users");
$row = mysql_fetch_assoc($tallypageviews);
echo 'Pages Viewed By All Members: ';
echo $row['total'];  

[JustinK101]

Do this instead:

 

$result = mysql_query("SELECT SUM(users_pagesviewed) as total FROM users");

$row = mysql_fetch_object($result);

 

echo 'Pages Viewed By All Members: ';

echo $row->total; 

[xProteuSx]

Thank you guys.  I got it to work.  Consider this topic SOLVED.

 

However, if you have a minute ...

 

Why is the 'total' alias needed?  (as in 'SELECT SUM(users_pagesviewed) as total FROM users').

 

Why can't it just be 'SELECT SUM(users_pagesviewed) FROM users'??

 

Just trying to learn the reasoning ... thanks again.

[pocobueno1388]

Something is needed to store the value in, so thats why the alias is there. Otherwise, what would you use to output the result? If you used $row['users_pageviewd'] it wouldn't make sense, because you didn't select that row from the database in the query.

 

Thats the best I can explain it...hopefully it makes sense

[JustinK101]

Yeah you just need to alias aggregate functions such as SUM in sql, just how it is. There is one way around it though:

 

$row = mysql_fetch_array($result);
echo $row[0];

 

But I don't recommend using this because the index position doesn't tell you column names, and makes the code harder to read.