SUM() help please

[RichardODreamweaver]

I have a relational recordset (which is now quite complex) and all I want to do is to create a single SUM value at the top of the dynamic table column.

I have looked around and understand the concept of SUM() and GROUP BY.

Does this mean that I need another recordset or can I build it into the existing one?

Do you definately need to include the GROUP BY statement? I don't want multiple values.

Here is a simplified version of the select code I have so far: (separate recordset) I don't think this needs to have the relational data inner join.

SELECT SUM(VALUE)

FROM NOTES

WHERE NOTES.EDITEDBY Like 'fielduser' AND NOTES.EDITDATE>='fielddatefrom' AND NOTES.EDITDATE<=Now()

GROUP BY ??????

Lastly, I believe the recordset has to be via a dynamic table (for multiple results) but I want just one value sitting in the column head

<td>VALUE <?php echo $row_recordset['SUM(NOTES.VALUE)']; ?></td>.

Much simpler in Excel!

[Newsgroup_User]

In a SQL statement you must give your SUM field a name like this

Select Sum(Fieldname) AS SumofFieldName

--
Paul Whitham
Certified Dreamweaver MX2004 Professional
Adobe Community Expert - Dreamweaver

Valleybiz Internet Design
www.valleybiz.net

"RichardODreamweaver" <webforumsuser@macromedia.com> wrote in message
news:eppq14$fjf$1@forums.macromedia.com...
>I have a relational recordset (which is now quite complex) and all I want
>to do
> is to create a single SUM value at the top of the dynamic table column.
>
> I have looked around and understand the concept of SUM() and GROUP BY.
>
> Does this mean that I need another recordset or can I build it into the
> existing one?
>
> Do you definately need to include the GROUP BY statement? I don't want
> multiple values.
>
> Here is a simplified version of the select code I have so far: (separate
> recordset) I don't think this needs to have the relational data inner
> join.
>
> SELECT SUM(VALUE)
>
> FROM NOTES
>
> WHERE NOTES.EDITEDBY Like 'fielduser' AND
> NOTES.EDITDATE>='fielddatefrom'
> AND NOTES.EDITDATE<=Now()
>
> GROUP BY ??????
>
> Lastly, I believe the recordset has to be via a dynamic table (for
> multiple
> results) but I want just one value sitting in the column head
>
> <td>VALUE <?php echo $row_recordset['SUM(NOTES.VALUE)']; ?></td>.
>
> Much simpler in Excel!
>
>
>

[RichardODreamweaver]

Sorry - should have mentioned - I am working in MySQL - is this the same?

[Newsgroup_User]

I believe so

--
Paul Whitham
Certified Dreamweaver MX2004 Professional
Adobe Community Expert - Dreamweaver

Valleybiz Internet Design
www.valleybiz.net

"RichardODreamweaver" <webforumsuser@macromedia.com> wrote in message
news:eps8lj$dhg$1@forums.macromedia.com...
> Sorry - should have mentioned - I am working in MySQL - is this the same?

[RichardODreamweaver]

Hmmm - tried this:

$query_SumOpportunities = "SELECT SUM(CONTNOTES.VALUE) AS SUMOF CONTNOTES.VALUE FROM CONTACTS INNER JOIN CONTNOTES ON CONTACTS.CONTACTID = CONTNOTES.NOTESCONTACTID WHERE CONTNOTES.TYPE = 'Opportunity' And CONTNOTES.COMPLETED = FALSE ORDER BY CONTNOTES.ACTDATE DESC";

But I get an unidentified error.

[Patch · Accepted solution]

At the start of your query, what appears after AS is the variable you want to assign the sum to, so try changing it to

= "SELECT SUM(CONTNOTES.VALUE) AS theTotal FROM CONTACTS INNER JOIN CONTNOTES .....

see if that works

Patch

[Newsgroup_User]

RichardODreamweaver wrote:

> Hmmm - tried this:
>
> $query_SumOpportunities = "SELECT SUM(CONTNOTES.VALUE) AS SUMOF
> CONTNOTES.VALUE FROM CONTACTS INNER JOIN CONTNOTES ON CONTACTS.CONTACTID =
> CONTNOTES.NOTESCONTACTID WHERE CONTNOTES.TYPE = 'Opportunity' And
> CONTNOTES.COMPLETED = FALSE ORDER BY CONTNOTES.ACTDATE DESC";
>
> But I get an unidentified error.
>
$query_SumOpportunities =
"SELECT SUM(CONTNOTES.VALUE) AS SUMOF, <--comma

Mick

[RichardODreamweaver]

Thanks Mick - at least I now get an identified error!

Now getting a message:

1140 Mixing of GROUP columns (Min(),Max(),Count()...) with no GROUP columns is illegal if there is no GROUP BY clause.

This goes back to my original question... If I just want one sum figure, how or why should I group?

[RichardODreamweaver]

Many thanks Patch

Cracked it....

Final code was:

$TotalValue_sumallopportunities = "0";
if (isset($TotalValueRuntime)) {
$TotalValue_sumallopportunities = (get_magic_quotes_gpc()) ? $TotalValueRuntime : addslashes($TotalValueRuntime);
}
mysql_select_db($database_dbcrm1, $dbcrm1);
$query_sumallopportunities = sprintf("SELECT SUM(CONTNOTES.VALUE) AS SUMOF, %s FROM CONTACTS INNER JOIN CONTNOTES ON CONTACTS.CONTACTID = CONTNOTES.NOTESCONTACTID WHERE CONTNOTES.TYPE = 'Opportunity' And CONTNOTES.COMPLETED = FALSE", $TotalValue_sumallopportunities);
$sumallopportunities = mysql_query($query_sumallopportunities, $dbcrm1) or die(mysql_error());
$row_sumallopportunities = mysql_fetch_assoc($sumallopportunities);
$totalRows_sumallopportunities = mysql_num_rows($sumallopportunities);

I then just put the variable into the header cell.