Not sure of your column names, or where your 'price' value is stored, but I
think you want something like what's below. Note that this also will show
all your products which may have had '0' orders so far (by using the LEFT
join).
<cfquery name="get_orders" datasource="#application.dsn#">
SELECT gp_product_templates.gp_name,
wp_products.price,
SUM(ca_order_items.quantity) AS qty
FROM gp_product_templates INNER JOIN
wp_products ON gp_product_templates.gp_id =
wp_products.wp_product_template_id LEFT OUTER JOIN
ca_order_items ON wp_products.wp_products_id =
ca_order_items.ca_orderitems_product_id
GROUP BY gp_product_templates.gp_name, wp_products.price
ORDER BY gp_product_templates.gp_name
</cfquery>
<table>
<tr>
<th>Product</th>
<th>Quantity Sold</th>
<th>Total Dollars</th>
</tr>
<cfoutput query="get_orders">
<tr>
<td>#gp_name#</td>
<td>#qty#</td>
<td>
#dollarFormat(qty * price)#
</td>
</tr>
</cfoutput>
</table>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320508
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
