Sum of Rows

• Zed

  Old Hand

  Points: 337

  More actions

  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

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  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