Post reply

Sum of Rows

  • April 26, 2021 at 9:35 am

    #3875768

    Hi Guys,

    How do i group the below code so it gives e a total duration for each person for each day. So for 07/08/2018 Dave Jones should show:

     

     

    -- Sample data
    IF OBJECT_ID('tempdb..#lab') IS NOT NULL DROP TABLE #lab
    SELECT * INTO #lab FROM (VALUES 
    ( '07/09/2018','Dave', 'Jones', '07/09/2018 09:56',  2301 ),
    ( '08/09/2018','Jim', 'Jones', '08/09/2018 09:56',  23561 ),
    ( '08/09/2018','Jim', 'Jones', '08/09/2018 09:56',  25201 ),
    ( '08/09/2018','Jim', 'Jones', '08/09/2018 09:56',  25601 ),
    ( '07/09/2018','Dave', 'Jones', '07/09/2018 10:45',  25641 )) d 

    ( DATE, FIRSTNAME, LASTNAME, LOGIN, DURATION)

    SELECT * FROM #lab
    ORDER BY DATE

  • April 26, 2021 at 10:12 am

    #3875794 

    IF OBJECT_ID('tempdb..#lab') IS NOT NULL DROP TABLE #lab
    SELECT * INTO #lab FROM (VALUES 
    ( '07/09/2018','Dave', 'Jones', '07/09/2018 09:56',  2301 ),
    ( '08/09/2018','Jim', 'Jones', '08/09/2018 09:56',  23561 ),
    ( '08/09/2018','Jim', 'Jones', '08/09/2018 09:56',  25201 ),
    ( '08/09/2018','Jim', 'Jones', '08/09/2018 09:56',  25601 ),
    ( '07/09/2018','Dave', 'Jones', '07/09/2018 10:45',  25641 )) d 

    ( DATE, FIRSTNAME, LASTNAME, LOGIN, DURATION)

    SELECT x.DATE, x.FIRSTNAME, x.LASTNAME, SUM(x.DURATION) DURATION
      FROM #lab x
     GROUP BY x.DATE, x.FIRSTNAME, x.LASTNAME
     ORDER BY x.DATE, x.LASTNAME, x.FIRSTNAME

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

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