Post reply

Select Top n Rows and Sum the Values

  • June 25, 2010 at 6:52 am

    #220533

    All,

    This is my first post, so please bear with me.

    What I'm trying to do is calculate precipitation values over a 1 hour, 3 hour, and 6 hour period. I have a table that shows accumulation values every 15 minutes. My thoughts were to sum the data values (T_DATA) for the first 4 rows (this would equal the past hour), sum the first 12 rows (this would equal the past three hours), and sum the first 24 rows (this would equal the past 6 hours).

    I can get this to work by setting up a view for each time span and then running a query on each view, but I have 8 stations to do, with 3 time spans each (24 views plus 24 queries). I would imagine that there is a more efficient way of doing this.

    My end goal is to get the final product in a table that looks like this:

    CREATE TABLE [dbo].[RiverRd_Precip_Totals] (

    [Total_1hr]float,

    [Total_3hr]float,

    [Total_6hr]float

    )

    GO

    I tried to follow Jeff Moden’s Forum Etiquette[/url] as closely as possible…please forgive me if I’ve missed anything. The following is the structure/data that I currently have:

    /****** Drop Table If It Exists ******/

    if object_id('DataWise.dbo.RiverRd_Precip_15') is not null

    drop table DataWise.dbo.RiverRd_Precip_15

    GO

    /****** Create Table ******/

    CREATE TABLE [DataWise].[dbo].[RiverRd_Precip_15] (

    [T_TIME] datetime,

    [T_DATA] float

    )

    GO

    /****** Insert Data Into Table ******/

    INSERT INTO DataWise.dbo.RiverRd_Precip_15

    (T_TIME, T_DATA)

    SELECT 'Jun 24 2010 3:00PM','.6' UNION ALL

    SELECT 'Jun 24 2010 2:45PM','1.5' UNION ALL

    SELECT 'Jun 24 2010 2:30PM','1.6' UNION ALL

    SELECT 'Jun 24 2010 2:15PM','1.4' UNION ALL

    SELECT 'Jun 24 2010 2:00PM','1.7' UNION ALL

    SELECT 'Jun 24 2010 1:45PM','1.3' UNION ALL

    SELECT 'Jun 24 2010 1:30PM','1.8' UNION ALL

    SELECT 'Jun 24 2010 1:15PM','1.2' UNION ALL

    SELECT 'Jun 24 2010 1:00PM','1.9' UNION ALL

    SELECT 'Jun 24 2010 12:45PM','1.1' UNION ALL

    SELECT 'Jun 24 2010 12:30PM','2.3' UNION ALL

    SELECT 'Jun 24 2010 12:15PM','2.2' UNION ALL

    SELECT 'Jun 24 2010 12:00PM','.1' UNION ALL

    SELECT 'Jun 24 2010 11:45AM','.8' UNION ALL

    SELECT 'Jun 24 2010 11:30AM','1.7' UNION ALL

    SELECT 'Jun 24 2010 11:15AM','1.6' UNION ALL

    SELECT 'Jun 24 2010 11:00AM','1.8' UNION ALL

    SELECT 'Jun 24 2010 10:45AM','1.5' UNION ALL

    SELECT 'Jun 24 2010 10:30AM','1.9' UNION ALL

    SELECT 'Jun 24 2010 10:15AM','1.4' UNION ALL

    SELECT 'Jun 24 2010 10:00AM','2.1' UNION ALL

    SELECT 'Jun 24 2010 9:45AM','1.3' UNION ALL

    SELECT 'Jun 24 2010 9:30AM','2.4' UNION ALL

    SELECT 'Jun 24 2010 9:15AM','1.2'

    GO

    There is no special formatting on the datetime field.

    I've tried running the query:

    SELECT TOP 4 SUM(T_DATA) As Total

    FROM [DataWise].[dbo].[RiverRd_Precip_15]

    But it just gives me the total of all the rows (this, I’m sure, is due to my lack of expertise when it comes to T-SQL).

    My intentions are to apply what I learn here and compile an SSIS package to run on a regular schedule.

    Any help would be greatly appreciated.

    Thank you.

  • June 25, 2010 at 7:07 am

    #1184975

    Is it always the last hour, the last 3 hours, the last 6 hours which you want to retrieve?

    As a single row?

    For each site?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • June 25, 2010 at 7:21 am

    #1184979

    /****** Drop Table If It Exists ******/

    --if object_id('#RiverRd_Precip_15') is not null

    drop table #RiverRd_Precip_15

    /****** Create Table ******/

    CREATE TABLE #RiverRd_Precip_15 (

    [T_TIME] datetime,

    [T_DATA] float

    )

    /****** Insert Data Into Table ******/

    INSERT INTO #RiverRd_Precip_15

    (T_TIME, T_DATA)

    SELECT 'Jun 24 2010 3:00PM','.6' UNION ALL

    SELECT 'Jun 24 2010 2:45PM','1.5' UNION ALL

    SELECT 'Jun 24 2010 2:30PM','1.6' UNION ALL

    SELECT 'Jun 24 2010 2:15PM','1.4' UNION ALL

    SELECT 'Jun 24 2010 2:00PM','1.7' UNION ALL

    SELECT 'Jun 24 2010 1:45PM','1.3' UNION ALL

    SELECT 'Jun 24 2010 1:30PM','1.8' UNION ALL

    SELECT 'Jun 24 2010 1:15PM','1.2' UNION ALL

    SELECT 'Jun 24 2010 1:00PM','1.9' UNION ALL

    SELECT 'Jun 24 2010 12:45PM','1.1' UNION ALL

    SELECT 'Jun 24 2010 12:30PM','2.3' UNION ALL

    SELECT 'Jun 24 2010 12:15PM','2.2' UNION ALL

    SELECT 'Jun 24 2010 12:00PM','.1' UNION ALL

    SELECT 'Jun 24 2010 11:45AM','.8' UNION ALL

    SELECT 'Jun 24 2010 11:30AM','1.7' UNION ALL

    SELECT 'Jun 24 2010 11:15AM','1.6' UNION ALL

    SELECT 'Jun 24 2010 11:00AM','1.8' UNION ALL

    SELECT 'Jun 24 2010 10:45AM','1.5' UNION ALL

    SELECT 'Jun 24 2010 10:30AM','1.9' UNION ALL

    SELECT 'Jun 24 2010 10:15AM','1.4' UNION ALL

    SELECT 'Jun 24 2010 10:00AM','2.1' UNION ALL

    SELECT 'Jun 24 2010 9:45AM','1.3' UNION ALL

    SELECT 'Jun 24 2010 9:30AM','2.4' UNION ALL

    SELECT 'Jun 24 2010 9:15AM','1.2'

    -- get these datetimes up-to-date

    UPDATE #RiverRd_Precip_15 SET T_TIME = DATEADD(hh, -1, DATEADD(dd, 1, T_TIME))

    ;WITH DataWithExtras AS (

    SELECT Recency = ROW_NUMBER() OVER(ORDER BY T_TIME DESC),

    *, Age_minutes = DATEDIFF(mi, T_TIME, GETDATE())

    FROM #RiverRd_Precip_15

    WHERE DATEDIFF(hh, T_TIME, GETDATE()) <= 6

    )

    SELECT

    [1 hour] = SUM(CASE WHEN Age_minutes <= 60 THEN T_DATA ELSE 0 END),

    [3 hour] = SUM(CASE WHEN Age_minutes <= 180 THEN T_DATA ELSE 0 END)

    FROM DataWithExtras

    Or this, but it's less fun;

    SELECT

    [1 hour] = SUM(CASE WHEN DATEDIFF(mi, T_TIME, GETDATE()) <= 60 THEN T_DATA ELSE 0 END),

    [3 hour] = SUM(CASE WHEN DATEDIFF(mi, T_TIME, GETDATE()) <= 180 THEN T_DATA ELSE 0 END)

    FROM #RiverRd_Precip_15

    WHERE DATEDIFF(hh, T_TIME, GETDATE()) <= 6

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • June 25, 2010 at 7:30 am

    #1184983

    wilsot (6/25/2010)

    I've tried running the query:

    SELECT TOP 4 SUM(T_DATA) As Total

    FROM [DataWise].[dbo].[RiverRd_Precip_15]

    That query is doing exactly what you're asking it to 😉

    SELECT SUM(a.t_data)

    FROM (SELECT TOP 4 *

    FROM DataWise.dbo.riverrd_precip_15) AS a

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • June 25, 2010 at 3:46 pm

    #1185288

    Hey guys...thanks for the quick response!

    I'll try this out and see what I come up with.

  • June 30, 2010 at 10:27 am

    #1187213

    Well...I got it up and running.

    I ended up importing all 8 precip tables (with 15 minute intervals), and then running the following script to update the values to a common table:

    UPDATE DataWise.dbo.CurrentData

    SET Total_1hr =(SELECT SUM(t_data)

    FROM (SELECT TOP 4 *

    FROM DataWise.dbo.RiverRd_Precip) AS a)

    Where T_SENSOR=103

    GO

    UPDATE DataWise.dbo.CurrentData

    SET Total_3hr =(SELECT SUM(t_data)

    FROM (SELECT TOP 12 *

    FROM DataWise.dbo.RiverRd_Precip) AS a)

    Where T_SENSOR=103

    GO

    UPDATE DataWise.dbo.CurrentData

    SET Total_6hr =(SELECT SUM(t_data)

    FROM (SELECT TOP 24 *

    FROM DataWise.dbo.RiverRd_Precip) AS a)

    Where T_SENSOR=103

    GO

    UPDATE DataWise.dbo.CurrentData

    SET Total_24hr =(SELECT SUM(t_data)

    FROM (SELECT TOP 96 *

    FROM DataWise.dbo.RiverRd_Precip) AS a)

    Where T_SENSOR=103

    GO

    (this is just the code for one station)

    This gives me 1 hr, 3 hr, 6 hr, and 24 hr values. It's also included in my SSIS package and is running smoothly.

    Thanks for all your help.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy