Highlighted

Summing Dollar Amounts from Query- Evaluate? Loop? CFSET? Let SQL DO IT?

New Here ,
Oct 07, 2020

Copy link to clipboard

Copied

Greetings.

 

I posted this question about 5 years ago. Adding up payments by client. I still have not used this function - long story.

 

So the query comes  from 2 tables- one fetches the orders and the other clients to display  the client billing hduring a given period.

 

SELECT order_ID, order_number, order_client_ID, order_client_fee, order_status_ID, order_report_sent_date, order_property_street, order_property_city, client_ID, client_company, client_status

FROM main_orders, lookup_clients

WHERE order_client_ID = client_ID

AND order_client_ID = #FORM.client_ID#

AND order_status_ID = 9

AND client_status = 1

AND (order_report_sent_date BETWEEN #Session.StartDate# AND #Session.EndDate#)

ORDER BY order_report_sent_date ASC

 

Each payment diplays as #DollarFormat(order_client_fee)#

 

I simply need a sum column. Any help would be apreciated. The simpler the better! Thanks in advance.

Adobe Community Professional
Correct answer by Charlie Arehart | Adobe Community Professional

There are of course many ways to do it, each with pros and cons, but in terms of "the simpler the better" and given that you already have the code in CFML, you can do it in one line of code, starting with using CF's valuelist, a nifty and perhap under-used function that returns a list of all the values in a given col (in a given query). With that, you're halfway home.

 

As there's no function in CFML to add up such values in a list, you could instead convert that to an array, and then use CF's arraysum. Then you could format it as dollars, like you show using. 

 

So assuming that select was in a cfquery with a name of "myquery", the one line of code is this:

 

#DollarFormat(ArraySum(listtoarray(ValueList(myQuery.order_client_fee))))#

 

Again, lots of other ways to do it (doing a "group by" in sql, using "member functions" in CFML which would appeal more to some tastes, etc.), but let us know if this suffices.

TOPICS
Advanced techniques

Views

42

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Summing Dollar Amounts from Query- Evaluate? Loop? CFSET? Let SQL DO IT?

New Here ,
Oct 07, 2020

Copy link to clipboard

Copied

Greetings.

 

I posted this question about 5 years ago. Adding up payments by client. I still have not used this function - long story.

 

So the query comes  from 2 tables- one fetches the orders and the other clients to display  the client billing hduring a given period.

 

SELECT order_ID, order_number, order_client_ID, order_client_fee, order_status_ID, order_report_sent_date, order_property_street, order_property_city, client_ID, client_company, client_status

FROM main_orders, lookup_clients

WHERE order_client_ID = client_ID

AND order_client_ID = #FORM.client_ID#

AND order_status_ID = 9

AND client_status = 1

AND (order_report_sent_date BETWEEN #Session.StartDate# AND #Session.EndDate#)

ORDER BY order_report_sent_date ASC

 

Each payment diplays as #DollarFormat(order_client_fee)#

 

I simply need a sum column. Any help would be apreciated. The simpler the better! Thanks in advance.

Adobe Community Professional
Correct answer by Charlie Arehart | Adobe Community Professional

There are of course many ways to do it, each with pros and cons, but in terms of "the simpler the better" and given that you already have the code in CFML, you can do it in one line of code, starting with using CF's valuelist, a nifty and perhap under-used function that returns a list of all the values in a given col (in a given query). With that, you're halfway home.

 

As there's no function in CFML to add up such values in a list, you could instead convert that to an array, and then use CF's arraysum. Then you could format it as dollars, like you show using. 

 

So assuming that select was in a cfquery with a name of "myquery", the one line of code is this:

 

#DollarFormat(ArraySum(listtoarray(ValueList(myQuery.order_client_fee))))#

 

Again, lots of other ways to do it (doing a "group by" in sql, using "member functions" in CFML which would appeal more to some tastes, etc.), but let us know if this suffices.

TOPICS
Advanced techniques

Views

43

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Oct 07, 2020 0
Adobe Community Professional ,
Oct 07, 2020

Copy link to clipboard

Copied

A sum column to display/calculate the sum of what? 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 07, 2020 0
Adobe Community Professional ,
Oct 07, 2020

Copy link to clipboard

Copied

There are of course many ways to do it, each with pros and cons, but in terms of "the simpler the better" and given that you already have the code in CFML, you can do it in one line of code, starting with using CF's valuelist, a nifty and perhap under-used function that returns a list of all the values in a given col (in a given query). With that, you're halfway home.

 

As there's no function in CFML to add up such values in a list, you could instead convert that to an array, and then use CF's arraysum. Then you could format it as dollars, like you show using. 

 

So assuming that select was in a cfquery with a name of "myquery", the one line of code is this:

 

#DollarFormat(ArraySum(listtoarray(ValueList(myQuery.order_client_fee))))#

 

Again, lots of other ways to do it (doing a "group by" in sql, using "member functions" in CFML which would appeal more to some tastes, etc.), but let us know if this suffices.

/Charlie (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 07, 2020 0
New Here ,
Oct 12, 2020

Copy link to clipboard

Copied

Charlie: Very much appreciate your solution. Works great! Thanks.

 

Norman B.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 12, 2020 0
Adobe Community Professional ,
Oct 12, 2020

Copy link to clipboard

Copied

Great to hear, Norman. And glad to have helped.

/Charlie (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 12, 2020 0