mysql SUM() is adding all records, as opposed to just ones being pulled...? why?

[mac007]

Hello, all:

 

well, I just realized my Sum() mysql-select statement is adding all records as oppposed to just the ones being returned. Problem is I have 2 select statements. This first one selects a set or records based on a variety of filtering-options...

 

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

 

 

and this select-statement, SUMS the "noorder" column, but it SUMS all records in db, as opposed to just the ones being pulled from statement above... how can I  link statements so it sums just records being pulled??

 

<?php $select = mysql_query("SELECT SUM(noorder) AS monthsum FROM ucn");
$monthSum=mysql_fetch_array($select);
echo $monthSum['monthsum']; ?>

 

 

Appreciate any help...

 

[Andy-H]

Thats because you are selecting all rows from that table as you have no "WHERE" clause in your SQL statement.

 

I assume you want it to sum the data from your $Recordset1 query?

 

Show the query an I can add it for you?

 

 

$Recordset1 = mysql_query("SELECT *, SUM(noorder) As monthsum FROM ucn WHERE blah = '" . $blah . "' LIMIT 1337");

 

[mac007]

Yes, Andy, I want to use the $Recordset1 query. Here's the whole $Recordset1 select statement, which is kind of  long cause I have a form-filtering setup to pull specific records based on form-choice submissions...

 

mysql_select_db($database_natureCityMembers, $natureCityMembers);

$agent = $_GET['agentid'];
$skillname = $_GET['skillname'];
$ani = $_GET['ani'];
$dnis = $_GET['dnis'];
$startdate1 = $_GET['startdate1'];
$daydate1 = $_GET['daydate1'];
$yeardate1 = $_GET['yeardate1'];
$startingdate1 = $yeardate1."-".$startdate1."-".$daydate1;
$startdate2 = $_GET['startdate2'];
$daydate2 = $_GET['daydate2'];
$yeardate2 = $_GET['yeardate2'];
$startingdate2 = $yeardate2."-".$startdate2."-".$daydate2;

$query_Recordset1 = "SELECT * FROM ucn WHERE 1";
if (!empty($_GET['agentid']))
	{
	$query_Recordset1 .= " AND agentid = '$agent'";
	}
if (!empty($_GET['skillname']))
	{
	$query_Recordset1 .= " AND skillname = '$skillname'";
	}
if (!empty($_GET['ani']))
	{
	$query_Recordset1 .= " AND ani = '$ani'";
	}
if (!empty($_GET['dnis']))
	{
	$query_Recordset1 .= " AND dnis = '$dnis'";
	}
if (!empty($_GET['startdate1']))
	{
	$query_Recordset1 .= " AND startdate BETWEEN '$startingdate1' AND '$startingdate2'";
	}
$query_Recordset1 .= " ORDER BY startdate DESC";


$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $natureCityMembers) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

 

Appreciate the help!

[Andy-H]

mysql_select_db($database_natureCityMembers, $natureCityMembers);

$agent = $_GET['agentid'];
$skillname = $_GET['skillname'];
$ani = $_GET['ani'];
$dnis = $_GET['dnis'];
$startdate1 = $_GET['startdate1'];
$daydate1 = $_GET['daydate1'];
$yeardate1 = $_GET['yeardate1'];
$startingdate1 = $yeardate1."-".$startdate1."-".$daydate1;
$startdate2 = $_GET['startdate2'];
$daydate2 = $_GET['daydate2'];
$yeardate2 = $_GET['yeardate2'];
$startingdate2 = $yeardate2."-".$startdate2."-".$daydate2;

$query_Recordset1 = "SELECT *, SUM(noorder) As monthSum FROM ucn WHERE 1";
if (!empty($_GET['agentid']))
	{
	$query_Recordset1 .= " AND agentid = '$agent'";
	}
if (!empty($_GET['skillname']))
	{
	$query_Recordset1 .= " AND skillname = '$skillname'";
	}
if (!empty($_GET['ani']))
	{
	$query_Recordset1 .= " AND ani = '$ani'";
	}
if (!empty($_GET['dnis']))
	{
	$query_Recordset1 .= " AND dnis = '$dnis'";
	}
if (!empty($_GET['startdate1']))
	{
	$query_Recordset1 .= " AND startdate BETWEEN '$startingdate1' AND '$startingdate2'";
	}
$query_Recordset1 .= " ORDER BY startdate DESC";


$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $natureCityMembers) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

//$monthSum = $row_Recordset1['monthSum'];

[mac007]

for real.. is that it?  let me try it... looks like you just added  the sum() into the main select statement...

[mac007]

hey, Andy.. it gave me this warning:

 

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

[Andy-H]

Hmm..

 

Try selecting the column names rather than * ??

 

 

 

 

[mac007]

hey, Andy: well, I worked out... kind of, I guess anyway; I ended up duplicating a whole new recordset off the original one... renaming all references by adding SUM at end of recordset-variables.

 

$query_RecordsetSUM = "SELECT *, SUM(noorder) AS monthSum FROM ucn WHERE 1";
if (!empty($_GET['agentid']))
	{
	$query_RecordsetSUM .= " AND agentid = '$agent'";
	}
if (!empty($_GET['skillname']))
	{
	$query_RecordsetSUM .= " AND skillname = '$skillname'";
	}
if (!empty($_GET['ani']))
	{
	$query_RecordsetSUM .= " AND ani = '$ani'";
	}
if (!empty($_GET['dnis']))
	{
	$query_RecordsetSUM .= " AND dnis = '$dnis'";
	}
if (!empty($_GET['startdate1']))
	{
	$query_RecordsetSUM .= " AND startdate BETWEEN '$startingdate1' AND '$startingdate2'";
	}
$query_RecordsetSUM .= " GROUP BY first ORDER BY startdate DESC";

$RecordsetSUM = mysql_query($query_RecordsetSUM, $natureCityMembers) or die(mysql_error());
$row_RecordsetSUM = mysql_fetch_assoc($RecordsetSUM);

 

and the SUM line I changed to this...

 

$monthSum = $row_RecordsetSUM['monthSum'];

 

 

I didnt try, listign the colum names... I did was able to get it thru at first by putting GROUP BY line at very end of select-statement, but before the ORDER BY, but then I noticed it was then GROUPING the records, so that if "John" appeared 8 times, it would only show 1 time. That's why I thought the only way woudl be to use the GROUP BY on a duplicated recordset.. and looks like it worked!

 

Thanks a lot, you got me in the right direction!

 

 

 

[Andy-H]

Why not just use the first record set and add a group by to a second query variable like:

 

mysql_select_db($database_natureCityMembers, $natureCityMembers);

$agent = $_GET['agentid'];
$skillname = $_GET['skillname'];
$ani = $_GET['ani'];
$dnis = $_GET['dnis'];
$startdate1 = $_GET['startdate1'];
$daydate1 = $_GET['daydate1'];
$yeardate1 = $_GET['yeardate1'];
$startingdate1 = $yeardate1."-".$startdate1."-".$daydate1;
$startdate2 = $_GET['startdate2'];
$daydate2 = $_GET['daydate2'];
$yeardate2 = $_GET['yeardate2'];
$startingdate2 = $yeardate2."-".$startdate2."-".$daydate2;

$query_Recordset1 = "SELECT * FROM ucn WHERE 1";
$query_RecordSum  = "SELECT SUM(noorder) As monthSum FROM ucn WHERE 1";
           $query_add = '';

if (!empty($_GET['agentid']))
	{
	$query_add .= " AND agentid = '$agent'";
	}
if (!empty($_GET['skillname']))
	{
	$query_add .= " AND skillname = '$skillname'";
	}
if (!empty($_GET['ani']))
	{
	$query_add .= " AND ani = '$ani'";
	}
if (!empty($_GET['dnis']))
	{
	$query_add .= " AND dnis = '$dnis'";
	}
if (!empty($_GET['startdate1']))
	{
	$query_add .= " AND startdate BETWEEN '$startingdate1' AND '$startingdate2'";
	}
           $query_RecordSum .= $query_add . " GROUP BY first ORDER BY startdate DESC";
   $query_Recordset1 .= $query_add . " ORDER BY startdate DESC";


$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $natureCityMembers) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

//recordsetSum
$RecordSUM = mysql_query($query_RecordSUM, $natureCityMembers) or die(mysql_error());
$row_RecordSUM = mysql_fetch_assoc($RecordSUM);

$monthSum = $row_RecordSUM['monthSum'];