Umm, add:
SUM([rushqty]) AS 'rushsub'
To your select, wouldn't that work?
On Wed, 2003-03-19 at 11:19, Mike Smith wrote:
Here is my query (MSSQL 2000):
SELECT [rushqty], [wooem], [rushglschk], [rushglsdate], [rushmtlchk],
[rushmtldate],
[rushpartschk], [rushpartsdate], [category],
CONVERT(nvarchar,[rushdate],101) AS 'rushdate',
CONVERT(nvarchar,[rushship],101) AS 'rushship', [wo], [rushcomm]
FROM [tblworush]
WHERE compdate IS NULL
ORDER BY [rushship]
I'm trying to use PHP to do the grouping and subtotals In my HTML I'm trying
to group by [rushship] (that works), and trying to subtotal (SUM) [rushqty]
for each grouping. My code below correctly SUMs only the last 2 values.
-----Original Message-----
From: Marek Kilimajer [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 19, 2003 8:19 AM
To: Mike Smith
Cc: [EMAIL PROTECTED]
Subject: Re: [PHP] Subtotal
SELECT *, SUM(units) subtotal FROM table GROUP BY date
- do you mean this?
Mike Smith wrote:
>I'm trying to present a simple SELECT statement "GROUPED" by date.
>Ex.
> WO UNITS OEM COMMENTS DATE
> 123 10 Me Notes 03/18/2003
> 456 5 You More Notes 03/18/2003
>Total 15
>...
>
>I have it working grouping the dates together, but I can't seem to get the
>Total Units for a group. You'll see $body .= " instead of echo... because
>this is sent as an email. Here is the relevent code:
>
><?php
>//table headers
>...
>while($row = mssql_fetch_array($result)) {
>// $row[10] = SHIP DATE (What I'm grouping by)
>// $row[0] = Unit Qty (What I want to SUM, based on each "Group")
>
>If ($lastship!='') { // skip the first record
> If ($lastship!=$row[10]) {
> $body .= "<tr>\n";
> $body .= "<td><strong>Total</strong>\n";
> $body .= "</td>\n";
> $body .= "<td align=center><strong>$total</strong>\n";
> $body .= "</td>\n";
> $body .= "<td colspan=5>\n";
> $body .= "</td>\n";
> $body .= "</tr>\n";
> $totqty=0;
> $lastqty=0;
>
> }
> $total=0;
>}
>
> $totqty=$lastqty+$row[0];
> $total = $totqty+$total;
> $lastship = $row[10];
> $lastqty=$row[0];
>
>$body .= "<tr>\n";
>$body .= "<td align=\"center\">\n";
>$body .= "$row[11]\n";
>$body .= "</td>\n";
>$body .= "<td align=\"center\">\n";
>$body .= "$row[0]\n";
>$body .= "</td>\n";
>$body .= "<td align=\"center\">\n";
>$body .= "$row[1]\n";
>$body .= "</td>\n";
>$body .= "<td>\n";
>$body .= "$row[12]\n";
>$body .= "</td>\n";
>$body .= "<td align=\"center\">\n";
>$body .= "$row[8]\n";
>$body .= "</td>\n";
>$body .= "<td align=\"center\">\n";
>$body .= "$row[9]\n";
>$body .= "</td>\n";
>$body .= "<td align=\"center\">\n";
>$body .= "$row[10]\n";
>$body .= "</td>\n";
>$body .= "</tr>\n";
>
>}
>...
>//close table, html
>?>
>
>Any help is appreciated.
>
>Thanks,
>Mike Smith
>
>
>
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
Adam Voigt ([EMAIL PROTECTED])
The Cryptocomm Group
My GPG Key: http://64.238.252.49:8080/adam_at_cryptocomm.asc
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
