Get sum of n rows from a column when sorted by another column

[thisisinsane1]

Hi, I'm hoping someone would kindly help with this - I am trying to return the sum of the 5 lastmodified rows in a table. So far I have tried the LIMIT function but it still just returns the sum of the entire column:

 

$totalvisits=mysql_query("SELECT SUM(pr) AS total FROM sh_urls ORDER BY lastmodified DESC LIMIT 0,5");

 

If there's important information I've missed out, let me know - thanks!

[redixx]

Can we see the table schema?

[thisisinsane1]

Yea sure, I'm new to this so I'm not totally sure what the term schema means, but i think it's this part:

 

$tot = mysql_result($totalvisits, 0, total);

 

[redixx]

It means the database table structure, with column names and their character types and such.

 

You can get it with this SQL:

SHOW CREATE TABLE `table_name_here`

 

EDIT: A schema would look like this:

CREATE TABLE `test` (
   `id` int(11) unsigned NOT NULL auto_increment,
   `name` varchar(30) NOT NULL
);

[thisisinsane1]

Ahhh! I was totally off the mark then.. here we go:

 

CREATE TABLE `sh_urls` (
`id` int(12) NOT NULL auto_increment,
`url` text NOT NULL,
`vi` int( NOT NULL default '0',
`pr` int( NOT NULL default '0',
`bl` tinyint(1) NOT NULL default '0',
`lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=487 DEFAULT CHARSET=utf8

 

So, I want to order by 'lastmodfied' and SUM the latest 5 results from column 'pr'

[jnvnsn]

$totalvisits="SELECT `pr` FROM sh_urls ORDER BY lastmodified DESC LIMIT 5";
$result = mysql_query($totalvisits) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
    printf($row['pr']."<br/>");
$total+=$row['pr'];
}
echo $total;

Just try.

[thisisinsane1]

That's working perfect! Thanks so much - really appreciate your help!!