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. > 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
