Solved

How to Loop Through a Table in SQL and apply a Calculation to each record

Posted on 2014-02-17
7
667 Views
Last Modified: 2014-02-17
Hi Experts,

I have MyTable with Column1 (X) containing test scores 1 through 100 (1,2,3,...100)

I want each row to be used to calculate (Y) in the formula:  Y = 2X+1

So my results will look like this:

X     Y
1     3
2     5
3     7
4     9
5     11
etc
etc

How would I write a loop for this?

Thanks you!
0
Comment
Question by:dunkin1969
7 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 300 total points
ID: 39865586
You don't need a loop and shouldn't use one.


SELECT x, x*2+1 AS y
FROM MyTable
0
 
LVL 18

Assisted Solution

by:lludden
lludden earned 200 total points
ID: 39865590
Why do a loop?

SELECT X, (2*[X])+1 AS Y FROM MyTable
0
 

Author Comment

by:dunkin1969
ID: 39865602
Thanks guys!  I couldn't see the forest through the trees!!!
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39865603
Why would you want to store this number, when you can compute it in a query at any time?  Generally, storing computed values is a bad idea.  Not always, but generally.
0
 

Author Comment

by:dunkin1969
ID: 39865611
oh by the way, let's say I don't have a table... and I want the same results using X = 1 through 100.  How would i do that?  Some type of counter?

Let me know if this should be posted as a new topic.  THANK YOU !!!
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 300 total points
ID: 39865659
Most efficient is a cross join to generate the needed numbers.  Code below gens numbers 0 thru 99, so I just add 1 to the gen'd values to get 1 thru 100.


;WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [10s].digit * 10 + [1s].digit AS tally
    FROM cteDigits [1s]
    CROSS JOIN cteDigits [10s]
)
SELECT (t.tally + 1) AS x, (t.tally*2)+1 AS y
FROM cteTally t
ORDER BY 1
0
 

Author Comment

by:dunkin1969
ID: 39865728
many thanks!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now