Post reply

Returning accumulated totals from multiple rows

  • July 17, 2009 at 1:22 pm

    #208475

    I have a table which contains several rows which among other things contain a datetime value, a user name varchar and string. I also have a function which extracts a duration from the string and returns the value as a float number of seconds.

    I use a view which returns the name, the date and (via the function) the duration. This view returns several rows for each name. I need to be able to return a single row for each name which contains the sum of the duration values for that name for all records in a specified date range. e.g.

    Data:

    2009-07-15 FRED 45

    2009-07-15 FRED 13

    2009-07-15 BILL 25

    2009-07-16 FRED 99

    Should return for a date range of 2009-07-15 yo 2009-07-15:

    2009-07-15 FRED 58

    2009-07-15 BILL 25

    Any help would be appreciated.

    Thanks.

  • July 17, 2009 at 2:05 pm

    #1026432

    I have to ask an obvious question... sorry if it's too obvious... do you know how to use SUM() and GROUP BY?

    --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)

  • July 17, 2009 at 2:19 pm

    #1026440

    Jeff has pointed you in the right direction.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • July 18, 2009 at 5:16 am

    #1026529

    Thanks guys,

    Yes, I've used Group By but until now I've never looked at Sum from a SQL perspective. Like everything, its quite obvious when you know how 🙂

  • July 18, 2009 at 7:17 am

    #1026548

    kylejw (7/18/2009)

    Thanks guys,

    Yes, I've used Group By but until now I've never looked at Sum from a SQL perspective. Like everything, its quite obvious when you know how 🙂

    No problem... didn't know what you knew or didn't know and didn't want to take a chance on accidently insulting someone new to the forum. Something like the following would do it.

    SELECT Date, Name, SUM(Duration) AS Total

    FROM yourtable

    GROUP BY Date, Name

    Since you're new, I'll suggest you take a pretty good look at the first link in my signature below. People get better answers a lot quicker when they prep their questions a bit.

    --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)

  • July 18, 2009 at 7:33 am

    #1026551

    Based on Jeff's SQL statement I'd like to extend it to include the start and end time you mentioned in your first post.

    Please note the way I used dateadd/datediff on your specified end date.

    If your date values are not normalized (meaning if you don't have all date values set to 12AM) then you'd need to convert the Date column in the GROUP By clause as well.

    -- prepare sample data

    declare @t table (date datetime, name varchar(30), duration int)

    insert into @t

    select '2009-07-15','FRED', 45 union all

    select '2009-07-15','FRED', 13 union all

    select '2009-07-15','BILL', 25 union all

    select '2009-07-16','FRED', 99

    -- define start and end time

    declare

    @start datetime,

    @end datetime

    set @start ='2009-07-15'

    set @end ='2009-07-15'

    SELECT convert(char(10),Date,120) as Date, Name, SUM(Duration) AS Total

    FROM @t yourtable

    where Date>=@start and Date < dateadd(dd,1,datediff(dd,0,@end)) -- include the start and end date

    GROUP BY Date, Name

    order by name desc -- sort as per expected result

    /* result set

    DateNameTotal

    2009-07-15FRED58

    2009-07-15BILL25

    expected result:

    Should return for a date range of 2009-07-15 yo 2009-07-15:

    2009-07-15 FRED 58

    2009-07-15 BILL 25

    */


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

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