You might try something like an inline view:
<snipped query>
inventory.service_name,
inventory.service_shipping,
inventory.service_desc,
sum_inventory.total
FROM customer_orders_prod_shiphold,
(SELECT SUM(service_shipping) AS total, service_id
FROM inventory
GROUP BY service_id) sum_inventory
LEFT OUTER JOIN inventory on
customer_orders_prod_shiphold.product_id = inventory.service_id
</snipped>
Don't forget to join sum_inventory.service_id to
customer_orders_prod_shiphold if needed.
If it is not needed, then take it out of the select and group by in
sum_inventory.
-jon
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 13, 2003 12:37 PM
To: CF-Talk
Subject: Quick SQL Sum ?
How can I easily nab the SUM of the inventory.service_shipping records
returned in this query via SQL? SUM blah AS cblah tosses an error, I
suppose because just one part is aggregate? Any quick ideas?
<cfquery name="get_productHOLDS" datasource="sddheinz">
SELECT
customer_orders_prod_shiphold.product_id,
customer_orders_prod_shiphold.qty,
customer_orders_prod_shiphold.line_total,
customer_orders_prod_shiphold.order_id,
inventory.service_name,
inventory.service_shipping,
inventory.service_desc
FROM customer_orders_prod_shiphold
LEFT OUTER JOIN inventory on
customer_orders_prod_shiphold.product_id = inventory.service_id
WHERE customer_orders_prod_shiphold.order_id =
<cfqueryparam cfsqltype="cf_sql_integer" value="#order_id#">
</cfquery>
Thanks!
Regards,
Eric J. Hoffman
DataStream Connexion
www.datastreamconnexion.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
