Jump to content


Photo

Query using SUM function?


  • Please log in to reply
2 replies to this topic

#1 rockonxox

rockonxox
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 16 November 2005 - 11:41 PM

The site is: [a href=\"http://www.visionseq.com/listhorsesboarded2.php\" target=\"_blank\"]http://www.visionseq.com/listhorsesboarded2.php[/a]

I am already have the list of horses boarded at my facility up and running. Now I would like to add all the rows in column 'boardfee' for each boarder. To do this I have used a SUM() function:
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]SELECT SUM( boardfee ) FROM boarded WHERE boarder = 'Duli'[/quote]

I don't have many boarders, so I don't have a huge problem with having to do a query for every single boarder. There are currently only four. This query works when I use it in phpmyadmin, but I have NO idea how to use PHP to show the query results on a site. I have tried reading tutorials and what I put together obviously doesn't work correctly (click the link above, you'll see what I mean).

Here is the code I used for the query... hopefully someone can lead me in the correct direction =)

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]$query = "SELECT SUM( boardfee ) FROM boarded
WHERE boarder = 'Duli'";
$result = mysql_query($query);
{
echo "<font color=white>";
echo "<b>Duli</b> owes $";
echo ($result);
echo " each month";
}[/quote]

I am not sure if this is neccessary, but here is my coding to get the list of horses boarded that is currently functioning as it should:
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]
$result = mysql_query("SELECT * FROM boarded",$db);
echo "<TABLE WIDTH=75% BORDER=2 BORDERCOLOR=BLACK CELLPADDING=2 CELLSPACING=0>";
echo"<TR BGCOLOR=ALICEBLUE><TD><B>Horse Name</B><TD><B>Located</B><TD><B>Owner</B><TD><B>Notes</B></TR>";
while ($myrow = mysql_fetch_array($result))
{
echo "<TR BGCOLOR=WHITE><TD valign=top>";
echo $myrow["hname"];
echo "<td valign=top>";
echo $myrow["located"];
echo "<TD valign=top>";
echo $myrow["boarder"];
echo "<TD valign=top width=45%>";
echo $myrow["notes"];
}
echo "</TABLE>";[/quote]

#2 rockonxox

rockonxox
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 17 November 2005 - 01:56 PM

Update: The query that had worked before is no longer working... [ SELECT sum(fee) FROM 'horses' WHERE boarder='Duli'; ] I'm not sure why all the sudden it doesn't work, maybe I copied what had worked down wrong or something.

ALSO I'm wondering if it is possible to add the sum of the boarders fees (like in the query shown) and add the column named 'extrafees' to the total of 'fees'. I am leaving no rows in these two columns null, they are all valued 0 or greater.

#3 sqlmc

sqlmc
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 18 November 2005 - 10:55 PM

I would try using an alias so it would go something like this:

SELECT SUM( boardfee ) AS board_sum
FROM boarded
WHERE boarder = 'Duli'";

/*************************************************************/

As for summing the two columns it would go something like this:

SELECT SUM( boardfee ) + SUM(ExtraFees) AS total_sum
FROM boarded
WHERE boarder = 'Duli'";

/*************************************************************/

Something else you may want to use with this or any other aggregate function is a group by clause.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users