Using a loop in SQL to populate the table (SQL Server)

Question

I am quite new with SQL and loops especially and need some help with the following problem.

I have a table like this:

SpotID EventID MaxTemp

123    1  45
236    1  109
69     1  18
123    2  216
236    2  29
69     2  84
123    3  91
236    3  457
69     3  280

I would like to generate a new table with the following output:

SpotID Over30 Over70 Over100 

123    3      2      1
236    2      2      2  
69     2      2      1

So what i am after is the count of how many times did the temperature exceed the limits of 30, 70 and 100 per SpotID for different EventIDs.

Is there a way to do this with a loop? My data set is obviously bigger and I am curious if how could i use something efficient.

Thank you very much.

Mike

What DBMS? Sql Server? Mysql?

Dave Zych
– Dave Zych

2014-11-04 22:53:31 +00:00
Commented Nov 4, 2014 at 22:53 — Dave Zych
– Dave Zych, Commented Nov 4, 2014 at 22:53
Sorry, forgot to add! It is Sql Server

MikeB
– MikeB

2014-11-04 23:22:12 +00:00
Commented Nov 4, 2014 at 23:22 — MikeB
– MikeB, Commented Nov 4, 2014 at 23:22

Gordon Linoff · Accepted Answer · 2014-11-04 22:54:29Z

5

You just need conditional aggregation:

select spotid,
       sum(case when maxtemp > 30 then 1 else 0 end) as over_30,
       sum(case when maxtemp > 70 then 1 else 0 end) as over_70
       sum(case when maxtemp > 100 then 1 else 0 end) as over_100
from likethis
group by spotid;

answered Nov 4, 2014 at 22:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

You accepted the version using a cursor instead? You have the privilege of accepting whichever answer you like, but in general, you should avoid cursors if you can.

Stephen Bodine · Accepted Answer · 2014-11-04 23:21:36Z

If you just want to learn how to use loops....

 DECLARE @DATA TABLE (
    SpotID INT,
    EventID INT,
    MaxTemp INT
    );

 DECLARE @NEWDATA TABLE (
    SpotID INT,
    T30 INT,
    T90 INT,
    T100 INT
    );

 DECLARE 
    @SPOT AS INT,
    @T30 AS INT,
    @T90 AS INT,
    @T100 AS INT;


 INSERT INTO @DATA VALUES
    (123, 1, 45 ),
    (236, 1, 109),
    (69 , 1, 18 ),
    (123, 2, 216),
    (236, 2, 29 ),
    (69 , 2, 84 ),
    (123, 3, 91 ),
    (236, 3, 457),
    (69 , 3, 280);


 DECLARE STATION CURSOR FOR SELECT SpotID FROM @DATA GROUP BY SpotID;
 OPEN STATION;
 FETCH NEXT FROM STATION INTO @SPOT;
 WHILE @@FETCH_STATUS = 0
 BEGIN
    SET @T30 = 0;
    SET @T90 = 0;
    SET @T100 = 0;

    SELECT 
    @T30 = SUM(CASE WHEN MaxTemp >  30  AND MaxTemp < 70  THEN 1 ELSE 0 END),
    @T90 = SUM(CASE WHEN MaxTemp >= 70  AND MaxTemp < 100 THEN 1 ELSE 0 END),
    @T100 = SUM(CASE WHEN MaxTemp >= 100 THEN 1 ELSE 0 END)
    FROM @DATA WHERE SpotID = @SPOT

    INSERT INTO @NEWDATA VALUES (@SPOT,@T30,@T90,@T100)
    FETCH NEXT FROM STATION INTO @SPOT;
 END;

 CLOSE STATION;
 DEALLOCATE STATION;

 SELECT * FROM @NEWDATA

Not anyway I would write the code requested, but this example shows how to create table variables, a simple cursor for looping, and writing to answers to variables that are loaded into a new table.

A lot of moving parts but it can give you insight to doing loops.

Stephen Bodine · Accepted Answer · 2014-11-04 23:30:41Z

0

One minor change to the previous post, my version only counts within each temp range, else the lower temps will count most temps not temps in that range.

 DECLARE @DATA TABLE (
    SpotID INT,
    EventID INT,
    MaxTemp INT
    )

 INSERT INTO @DATA VALUES
    (123, 1, 45 ),
    (236, 1, 109),
    (69 , 1, 18 ),
    (123, 2, 216),
    (236, 2, 29 ),
    (69 , 2, 84 ),
    (123, 3, 91 ),
    (236, 3, 457),
    (69 , 3, 280)

 SELECT 
    SpotID,
    SUM(CASE WHEN MaxTemp >  30  AND MaxTemp < 70  THEN 1 ELSE 0 END) AS OVER_30,
    SUM(CASE WHEN MaxTemp >= 70  AND MaxTemp < 100 THEN 1 ELSE 0 END) AS OVER_70,
    SUM(CASE WHEN MaxTemp >= 100 THEN 1 ELSE 0 END) AS OVER_100
 FROM 
    @DATA
 GROUP BY
    SpotID

edited Nov 4, 2014 at 23:30

answered Nov 4, 2014 at 23:05

Stephen Bodine

5194 silver badges10 bronze badges

6 Comments

Stephen Bodine Over a year ago

Question do you have PWS?

MikeB Over a year ago

Thank you for the answer Stephen. I am sorry but I am not sure I know what a PWS is so I cannot asnwer I am afraid.

Stephen Bodine Over a year ago

PWS = Personal Weather Station. I have had been collecting weather data from mine for the past 5 years until the last storm (90MPH winds) cased flying debris to completely remove my station from its mast.

Stephen Bodine Over a year ago

Added a second answer that uses loops in case you are interested in how to do loops with cursors.

MikeB Over a year ago

No, I do not have PWS, it is just some random project (Semi-work related.. ) but i would like to learn more about SQL hence my question. I am sorry to hear about the damage to the station! Thanks so much for the loop example! I would like to learn how to use them so this definitely helps! Thanks again!

|

Collectives™ on Stack Overflow

Using a loop in SQL to populate the table (SQL Server)

3 Answers 3

1 Comment

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related