RE: [PHP] Subtotal
This query:
***denotes new
SELECT ***SUM([rushqty]) AS 'rushsub'***, [rushqty], [wooem], [rushglschk],
[rushglsdate], [rushmtlchk], [rushmtldate],
[rushpartschk], [rushpartsdate], [category],
CONVERT(nvarchar,[rushdate],101) AS 'rushdate',
CONVERT(nvarchar,[rushship],101) AS 'rushship', [wo], [rushcomm]
FROM [junction].[dbo].[tblworush]
WHERE compdate IS NULL
***GROUP BY [rushship], [rushdate], [rushqty], [wooem], [rushglschk],
[rushglsdate], [rushmtlchk], ***[rushmtldate], [rushpartschk],
[rushpartsdate], [category],
[wo], [rushcomm]
ORDER BY [rushship]
Returns...
rushsub rushqty ... rushdate...
20 20 03/12/2003 ...
20 20 03/17/2003
70 70 03/17/2003
I still need (I think) to programatically subtotal [rushqty] rather than
having SQL do it. Since my grouping is working I could do a SELECT
[rushdate] ,SUM([rushqty]) AS 'rushsub' ... GROUP BY [rushdate] and put it
in an array. I was hoping to just have PHP handle the math. I'll have to try
it, but I think the GROUP BY rushdate may pose a problem, since the dates
will have different timestamps.
-Original Message-
From: Adam Voigt [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 19, 2003 8:37 AM
To: Mike Smith
Cc: [EMAIL PROTECTED]
Subject: RE: [PHP] Subtotal
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.
>WOUNITSOEMCOMMENTSDATE
>123 10MeNotes03/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:
>
>//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 .= "\n";
> $body .= "Total\n";
> $body .= "\n";
> $body .= "$total\n";
> $body .= "\n";
> $body .= "\n";
> $body .= "\n";
> $body .= "\n";
> $totqty=0;
> $lastqty=0;
>
> }
> $total=0;
>}
>
> $totqty=$lastqty+$row[0];
> $total = $totqty+$total;
> $lastship = $row[10];
> $lastqty=$row[0];
>
>$body .= "\n";
>$body .= "\n";
>$body .= "$row[11]\n";
>$body .= "\n";
>$body .= "\n";
>$body .= "$row[0]\n";
>$body .= "\n";
>$body .= "\n";
>$body .= "$row[1]\n";
>$body .= "\n";
>$body .= "\n";
>$body .= "$row[12]\n";
>$body .= "\n";
>$body .= "\n";
>$body .= "$row[8]\n";
>$body .= "\n";
>$body .= "\n";
>$body .= "$row[9]\n";
>$body .= "\n";
>$body .= "\n";
>$body .= "$row[10]\n";
>$body .= "\n";
>$body .= "\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
RE: [PHP] Subtotal
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.
>WOUNITSOEMCOMMENTSDATE
>123 10MeNotes03/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:
>
>//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 .= "\n";
> $body .= "Total\n";
> $body .= "\n";
> $body .= "$total\n";
> $body .= "\n";
> $body .= "\n";
> $body .= "\n";
> $body .= "\n";
> $totqty=0;
> $lastqty=0;
>
> }
> $total=0;
>}
>
> $totqty=$lastqty+$row[0];
> $total = $totqty+$total;
> $lastship = $row[10];
> $lastqty=$row[0];
>
>$body .= "\n";
>$body .= "\n";
>$body .= "$row[11]\n";
>$body .= "\n";
>$body .= "\n";
>$body .= "$row[0]\n";
>$body .= "\n";
>$body .= "\n";
>$body .= "$row[1]\n";
>$body .= "\n";
>$body .= "\n";
>$body .= "$row[12]\n";
>$body .= "\n";
>$body .= "\n";
>$body .= "$row[8]\n";
>$body .= "\n";
>$body .= "\n";
>$body .= "$row[9]\n";
>$body .= "\n";
>$body .= "\n";
>$body .= "$row[10]\n";
>$body .= "\n";
>$body .= "\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
RE: [PHP] Subtotal
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.
>WOUNITSOEMCOMMENTSDATE
>123 10MeNotes03/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:
>
>//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 .= "\n";
> $body .= "Total\n";
> $body .= "\n";
> $body .= "$total\n";
> $body .= "\n";
> $body .= "\n";
> $body .= "\n";
> $body .= "\n";
> $totqty=0;
> $lastqty=0;
>
> }
> $total=0;
>}
>
> $totqty=$lastqty+$row[0];
> $total = $totqty+$total;
> $lastship = $row[10];
> $lastqty=$row[0];
>
>$body .= "\n";
>$body .= "\n";
>$body .= "$row[11]\n";
>$body .= "\n";
>$body .= "\n";
>$body .= "$row[0]\n";
>$body .= "\n";
>$body .= "\n";
>$body .= "$row[1]\n";
>$body .= "\n";
>$body .= "\n";
>$body .= "$row[12]\n";
>$body .= "\n";
>$body .= "\n";
>$body .= "$row[8]\n";
>$body .= "\n";
>$body .= "\n";
>$body .= "$row[9]\n";
>$body .= "\n";
>$body .= "\n";
>$body .= "$row[10]\n";
>$body .= "\n";
>$body .= "\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
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.
WOUNITSOEMCOMMENTSDATE
123 10MeNotes03/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:
If ($lastship!='') { // skip the first record
If ($lastship!=$row[10]) {
$body .= "\n";
$body .= "Total\n";
$body .= "\n";
$body .= "$total\n";
$body .= "\n";
$body .= "\n";
$body .= "\n";
$body .= "\n";
$totqty=0;
$lastqty=0;
}
$total=0;
}
$totqty=$lastqty+$row[0];
$total = $totqty+$total;
$lastship = $row[10];
$lastqty=$row[0];
$body .= "\n";
$body .= "\n";
$body .= "$row[11]\n";
$body .= "\n";
$body .= "\n";
$body .= "$row[0]\n";
$body .= "\n";
$body .= "\n";
$body .= "$row[1]\n";
$body .= "\n";
$body .= "\n";
$body .= "$row[12]\n";
$body .= "\n";
$body .= "\n";
$body .= "$row[8]\n";
$body .= "\n";
$body .= "\n";
$body .= "$row[9]\n";
$body .= "\n";
$body .= "\n";
$body .= "$row[10]\n";
$body .= "\n";
$body .= "\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
