Post reply

query: FIND the MAX of the SUM of two values

  • November 20, 2009 at 8:56 am

    #214040

    Hi,

    I guess it's obvious that I'm not an SQL expert; I need help with a basic query:

    --I have a DB called 'CIMPLICITY'

    --The table is called 'DATA_LOG'

    --DATA_LOG's rows consist of 3 columns: 'timestamp', 'point_id', and '_val'

    I need to find the MAXIMUM value of the sum of two 'point_id's '_val's that occur at the same 'timestamp'.

    This is an application to find the maximum wattage of two power meters. I am logging the data via an HMI. Each power meter logs their instaneous watts into SQL. There are two power meters per building, so I need to find the max watts per building. I hope this helps explain my situation!!

    Can someone please help me with this?

    Scott Cheney

  • November 20, 2009 at 9:01 am

    #1082293

    Please post some sample data as mentioned in the below article

    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

    It would help us to understand the data in the underlying table and also start working on solving the query rather than building a test data from scratch (which may or may not match your Data)

    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • November 20, 2009 at 9:07 am

    #1082298

    I'm not clear on the original request.

    I gather that point IDs are the ID numbers for the meters. Is that correct?

    What data goes into "timestamp"? Is it a datetime entry? Just a date? Date and hour?

    Are there multiple entries per timestamp per point ID? If not, what are you summing up before looking for the higher value?

    Do you have something that ties the point IDs together so you can tell which ones are in the same building?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • November 20, 2009 at 9:10 am

    #1082302

    Agree, I must. Need DDL for the table, sample data, and expected results based on sample data.

    Follow the instructions in the article that Bru provided (also happens to be the first article I reference below in my signature block) and you will get better results and as a bonus, tested code.

  • November 20, 2009 at 7:04 pm

    #1082579

    scheney-1152259 (11/20/2009)

    Hi,

    I guess it's obvious that I'm not an SQL expert; I need help with a basic query:

    --I have a DB called 'CIMPLICITY'

    --The table is called 'DATA_LOG'

    --DATA_LOG's rows consist of 3 columns: 'timestamp', 'point_id', and '_val'

    I need to find the MAXIMUM value of the sum of two 'point_id's '_val's that occur at the same 'timestamp'.

    This is an application to find the maximum wattage of two power meters. I am logging the data via an HMI. Each power meter logs their instaneous watts into SQL. There are two power meters per building, so I need to find the max watts per building. I hope this helps explain my situation!!

    Can someone please help me with this?

    Scott Cheney

    Not quite enough info, Scott... for example... what identifies which point_id's that go with each building?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 20, 2009 at 11:06 pm

    #1082609

    Scott,

    I agree with the other participants that there is not enough information to provide the exact solution. The following script, however, may help you get started:

    set nocount on

    declare @table table (stamp datetime, point int, val int)

    declare @i int

    set @i = 0

    while @i < 5

    begin

    insert into @table select stamp = getdate(), point = 1, val = cast(rand() * (9999 - 1000) + 1000 as int)

    insert into @table select stamp = getdate(), point = 2, val = cast(rand() * (9999 - 1000) + 1000 as int)

    waitfor delay '00:00:01'

    set @i = @i + 1

    end

    print 'sample data'

    select * from @table

    print 'sum of val per stamp'

    select stamp, sum_val = sum(val) from @table group by stamp

    print 'stamp with max sum of val'

    select t.stamp, max_sum_val = sum(t.val)

    from @table t

    where t.stamp = (select top 1 stamp from @table group by stamp order by sum(val) desc)

    group by t.stamp

    set nocount off

    One execution of the script produced the following results:

    sample data

    stamp point val

    ----------------------- ----------- -----------

    2009-11-20 23:54:23.580 1 3023

    2009-11-20 23:54:23.580 2 5902

    2009-11-20 23:54:24.580 1 8587

    2009-11-20 23:54:24.580 2 1462

    2009-11-20 23:54:25.580 1 2144

    2009-11-20 23:54:25.580 2 7482

    2009-11-20 23:54:26.580 1 3313

    2009-11-20 23:54:26.580 2 4419

    2009-11-20 23:54:27.580 1 4851

    2009-11-20 23:54:27.580 2 1957

    sum of val per stamp

    stamp sum_val

    ----------------------- -----------

    2009-11-20 23:54:23.580 8925

    2009-11-20 23:54:24.580 10049

    2009-11-20 23:54:25.580 9626

    2009-11-20 23:54:26.580 7732

    2009-11-20 23:54:27.580 6808

    stamp with max sum of val

    stamp max_sum_val

    ----------------------- -----------

    2009-11-20 23:54:24.580 10049

    I hope this helps.

    GR

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

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