Solved

# Performing calculations in MSSQL rather than CF

Posted on 2005-03-21
Medium Priority
244 Views
While creating tabel in MSSQL I noticed the "Formula" field. How can I use this ito calculate "TotalVotes (SUM(V1 + V2 + V3 + V4 + V5 + V6 + V7+V8 + V9 + V10)" and "AverageVotes (<cfset step1 = (1 * #voteone#) + (2 * #votetwo#) + (3 * #votethree#) + (4 * #votefour#) + (5 * #votefive#)>
<cfset average = step1 / #total#>)" instead of doing it on the CF side?

0
Question by:jollymon6672
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 5

LVL 7

Expert Comment

ID: 13592632
SUM(V1 + V2 + V3 + V4 + V5 + V6 + V7+V8 + V9 + V10) AS TotalVotes, AVG(V1 + V2 + V3 + V4 + V5 + V6 + V7+V8 + V9 + V10) AS AverageVotes

-- Ian
0

Author Comment

ID: 13593390
the following error:

ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'AS'.
0

LVL 7

Expert Comment

ID: 13593469
0

Author Comment

ID: 13593516
Are these valid:

SUM(V1 + V2 + V3 + V4 + V5 + V6 + V7 + V8 + V9 + V10) AS TotalledVotes
WHERE VID=#URL.vid#

and

AVG(V1 + V2 + V3 + V4 + V5 + V6 + V7+V8 + V9 + V10) AS Average
SET AverageVote=#Average#
WHERE VID=#URL.vid#
0

LVL 7

Expert Comment

ID: 13594048
Nope. You can't SELECT in the UPDATE. Instead of having two columns (AverageVote) and (TotalVote) in the table, create column aliases when you use the data:

SELECT *,SUM(V1 + V2 + V3 + V4 + V5 + V6 + V7 + V8 + V9 + V10) AS TotalVotes,AVG(V1 + V2 + V3 + V4 + V5 + V6 + V7+V8 + V9 + V10) AS AverageVotes
WHERE VID = #URL.VID#

This would be done in all of your SELECT tags where you need TotalledVotes and Average
0

Author Comment

ID: 13594789

SET AverageVote=AVG(V1 + V2 + V3 + V4 + V5 + V6 + V7+V8 + V9 + V10) AS Average
WHERE VID=#URL.vid#

SET TotalVote=SUM(V1 + V2 + V3 + V4 + V5 + V6 + V7 + V8 + V9 + V10) AS TotalledVotes
WHERE VID=#URL.vid#
0

LVL 7

Expert Comment

ID: 13595303
You could do it like:

<cfquery name="totals">
SELECT SUM(V1 + V2 + V3 + V4 + V5 + V6 + V7 + V8 + V9 + V10) AS TotalVotes,AVG(V1 + V2 + V3 + V4 + V5 + V6 + V7+V8 + V9 + V10) AS AverageVotes
WHERE VID = #URL.VID#
</cfquery>

<cfquery>
WHERE VID=#URL.vid#;
WHERE VID=#URL.vid#
</cfquery>

But having those two columns in the table is unnecessary. You can use the SUM() and AVG() functions when you want to use the data in those two columns and get rid of the two columns altogether. By doing it this way, you won't have to worry about keeping the data in the total columns synchronized.

-- Ian
0

Author Comment

ID: 13774847
Can this be used for Access as well?
0

LVL 7

Accepted Solution

black0ps earned 150 total points
ID: 13775817
As far as I know, SUM and AVG should work in Access. Have you given it a try?
-- Ian
0

Author Comment

ID: 13785750
I tried it and I got the same results for both SUM and AVG which does not seem correct.

0

LVL 7

Expert Comment

ID: 13785874
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readinâ€¦
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice tâ€¦
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as formâ€¦
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastrâ€¦
###### Suggested Courses
Course of the Month12 days, 5 hours left to enroll