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
  • Last Modified:

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
Asked:
jollymon6672
  • 6
  • 5
1 Solution
 
black0psCommented:
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
 
jollymon6672Author Commented:
the following error:

ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'AS'.
0
 
black0psCommented:
post your SQL code
0
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
jollymon6672Author Commented:
Are these valid:

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

and

UPDATE tbl_Votes
AVG(V1 + V2 + V3 + V4 + V5 + V6 + V7+V8 + V9 + V10) AS Average
SET AverageVote=#Average#
WHERE VID=#URL.vid#
0
 
black0psCommented:
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
FROM tbl_Votes
WHERE VID = #URL.VID#

This would be done in all of your SELECT tags where you need TotalledVotes and Average
0
 
jollymon6672Author Commented:
I am trying to get the totalVote and AverageVote to update the table. What about this?

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


UPDATE tbl_Votes
    SET TotalVote=SUM(V1 + V2 + V3 + V4 + V5 + V6 + V7 + V8 + V9 + V10) AS TotalledVotes
    WHERE VID=#URL.vid#
0
 
black0psCommented:
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
FROM tbl_Votes
WHERE VID = #URL.VID#
</cfquery>

<cfquery>
UPDATE tbl_Votes
    SET AverageVote=#totals.TotalVotes#
    WHERE VID=#URL.vid#;
UPDATE tbl_Votes
    SET TotalVote=#totals.AverageVotes#
    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
 
jollymon6672Author Commented:
Can this be used for Access as well?
0
 
black0psCommented:
As far as I know, SUM and AVG should work in Access. Have you given it a try?
-- Ian
0
 
jollymon6672Author Commented:
I tried it and I got the same results for both SUM and AVG which does not seem correct.

0
 
black0psCommented:
Paste your SQL.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now