Select Top n Rows and Sum the Values

  • 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.

  • 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

  • /****** 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

  • 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/

  • Hey guys...thanks for the quick response!

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

  • 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