Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 251

# Performing calculations in MSSQL rather than CF

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
jollymon6672
• 6
• 5
1 Solution

Commented:
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 Commented:
the following error:

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

Commented:
0

Author Commented:
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

Commented:
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 Commented:

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

Commented:
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 Commented:
Can this be used for Access as well?
0

Commented:
As far as I know, SUM and AVG should work in Access. Have you given it a try?
-- Ian
0

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

0

Commented: