Brian,
You can get this to work with left joins and using sum() "Data format you
want would appear
like
Options 1
Prt_type Prt Stock Total
planeparts wingZ 9 14
planeparts rudderQ 3 14
planeparts engineRR 2 14
You dont want to repeat totals so its better to do this in 2 queries.
Get Parts in one query and Totals in the second.
2nd query.. Select sum(Stock) from where Prt_id=1
Option 2
You can write a stored procedure and switch between 2 queries(Parts and
Total)
to build a result set.. This is cleaner and faster. (BEST)
Option 3
You can do this is ColdFusion using QUERY Functions and Structure
notations.
Joe
Certified Advanced ColdFusion Developer
[EMAIL PROTECTED]
-----Original Message-----
From: Brian Scandale [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, July 10, 2002 10:35 PM
To: CF-Talk
Subject: SQL question...
I now this is quite simple... however I need clues to get me started...
How does one go about breaking out the various subtotals from a dataset?
Example:
In this example table there are multiples of the same parttype.
parttype partname instock
-------- -------- -------
carparts mufflerX 25
carparts mufflerY 15
carparts engineSA 12
carparts wheel12 27
planeparts wingZ 9
planeparts rudderQ 3
planeparts engineRR 2
Would typically do something like this in CF
<query="thelist">
Select *
From parts
Order by parttype
</query>
<cfoutput query="thelist">
<tr><td>#thelist.PartType#</td><td>#thelist.PartName#</td></tr>
</cfoutput>
Would like to be able to generate subtotals...like this:
parttype partname instock
-------- -------- -------
carparts mufflerX 25
carparts mufflerY 15
carparts engineSA 12
carparts wheel12 27
SUBTOTAL --------- -----
79
planeparts wingZ 9
planeparts rudderQ 3
planeparts engineRR 2
SUBTOTAL ----------- ------
14
Thanks for any and all hints. ;-)
Brian
______________________________________________________________________
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
