?
Solved

Performing calculations in MSSQL rather than CF

Posted on 2005-03-21
11
Medium Priority
?
244 Views
Last Modified: 2013-12-24
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
Comment
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
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 7

Expert Comment

by:black0ps
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

by:jollymon6672
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

by:black0ps
ID: 13593469
post your SQL code
0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

Author Comment

by:jollymon6672
ID: 13593516
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
 
LVL 7

Expert Comment

by:black0ps
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
FROM tbl_Votes
WHERE VID = #URL.VID#

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

Author Comment

by:jollymon6672
ID: 13594789
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
 
LVL 7

Expert Comment

by:black0ps
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
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
 

Author Comment

by:jollymon6672
ID: 13774847
Can this be used for Access as well?
0
 
LVL 7

Accepted Solution

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

by:jollymon6672
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

by:black0ps
ID: 13785874
Paste your SQL.
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

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…
Article by: kevp75
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

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question