how to count the sum of the list of products prices ( ColdFusion )
here is the query:
<cfquery name="GET_SHIP_ROW" datasource="#DSN2#">
SELECT
SR.*,
S.STOCK_CODE
FROM
SHIP_ROW SR,
#dsn3_alias#.STOCKS S
WHERE
SR.STOCK_ID = S.STOCK_ID AND
SR.SHIP_ID = #attributes.ship_id#
ORDER BY
SR.SHIP_ROW_ID
</cfquery>
for example i have a loop code of the prices:
<cfloop from="#satir_start#" to="#satir_end#" index="i">
<cfif i lte get_ship_row.recordcount>
<cfscript>
if(len(get_ship_row.discount[i]))indirim = get_ship_row.discount[i]; else indirim = 0;
adim_1 = get_ship_row.amount[i] * get_ship_row.price[i];
adim_2 = (adim_1/100)*(100-indirim);
adim_3 = adim_2*(get_ship_row.tax[i]/100);
adim_4 = adim_2+adim_3;
</cfscript>
<cfquery name开发者_开发知识库="GET_BARCODE" datasource="#DSN3#">
SELECT
BARCOD
FROM
PRODUCT
WHERE
PRODUCT_ID = #get_ship_row.product_id[i]#
</cfquery>
<table>
<tr>
<td style="width:30mm;"><cfoutput>#get_ship_row.stock_code[i]#</cfoutput></td>
<td style="width:50mm;"><cfoutput>#left(get_ship_row.name_product[i],53)#</cfoutput></td>
<td style="width:25mm;" align="right"><cfoutput>#get_ship_row.amount[i]# #get_ship_row.unit[i]#</cfoutput></td>
<td style="width:25mm;" align="right"><cfoutput>#TLFormat(get_ship_row.price[i])#</cfoutput> TL</td>
<td style="width:35mm;" align="right"><cfoutput>#TLFormat(get_ship_row.amount[i] * get_ship_row.price[i])#</cfoutput> TL</td>
</tr>
</table>
</cfif>
</cfloop>
there are displayed a list of the names and its prices, all i want to count, is their sum of prices, i mean of all the products. how do i dow it? thx for help!
I'm just taking a guess based on your (presumed) database structure. I'd recommend moving the calculation completely into the query so the db server is doing the work it's best at. Manipulating something like this in CF is less than optimal.
You'll have to test this, but I think it's close enough to get started:
<cfquery name="GET_SHIP_ROW" datasource="#DSN2#">
SELECT
SR.*,
S.STOCK_CODE,
( (sr.amount*sr.price) * ( (100-discount)/100 ) * ( tax/100 ) ) as itemCost
FROM
SHIP_ROW SR,
#dsn3_alias#.STOCKS S
WHERE
SR.STOCK_ID = S.STOCK_ID AND
SR.SHIP_ID = #attributes.ship_id#
ORDER BY
SR.SHIP_ROW_ID
</cfquery>
Once you have the line item totals in the recordset, running a query of queries to get the sum of all line items is pretty straightforward:
<cfquery name="total" dbtype="query">
select sum(itemCost) as shipmentTotal from GET_SHIP_ROW
</cfquery>
Does that help?
精彩评论