Group and total by column Name

Question

Group and total by column Name

SSCommitted

Points: 1643

July 23, 2019 at 10:39 am

Guys, How do i group the below by type and date so i have a total for each day for each person by each type. So the desired results should look like:

INTO #SampleData2

FROM (

VALUES ('2019-01-01', 'Dave', 'Break', 797),

('2019-01-01', 'Dave', 'Break', 746),

('2019-01-01', 'Dave', 'Break', 511),

('2019-01-01', 'Bethan', 'Break', 631),

('2019-01-01', 'Bethan', 'Break', 751),

('2019-01-02', 'Bethan', 'Break', 400),

('2019-01-02', 'Bethan', 'Break', 405),

('2019-01-02', 'Bethan', 'Training', 500),

('2019-01-02', 'Bethan', 'Training', 456)


) AS d (Created, Name, Type, Durationinsecs);

SELECT Created, Name, Type, Durationinsecs as [duration], 

CONVERT(varchar, DATEADD(ss, Durationinsecs, 0), 108)  as [Minutes]  
from #SampleData2

Group by Type, Created, Name, Durationinsecs

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

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

mohan.muralid SSC Journeyman Points: 99 More actions · Answer 1

Hi,

I have just modified your query and, please find the below:-

-- converting durationinsecs into int and summing the value
SELECT Created, Name, Type, sum(cast (Durationinsecs as int)) as [duration], 

CONVERT(varchar, DATEADD(ss, sum(cast (Durationinsecs as int)), 0), 108)  as [Minutes]  
from SampleData2
-- in group by removed durationinsecs
Group by Type, Created, Name

Phil Parkin SSC Guru Points: 246695 More actions · Answer 2

Declaring variables as Varchar without an explicit length is considered bad practice and can lead to unexpected results.

Use VARCHAR(n) (where n is the longest expected length) instead.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Thom A SSC Guru Points: 99126 More actions · Answer 3

mohan.muralid wrote:

Hi, I have just modified your query and, please find the below:-

-- converting durationinsecs into int and summing the value
SELECT Created, Name, Type, sum(cast (Durationinsecs as int)) as [duration], 

CONVERT(varchar, DATEADD(ss, sum(cast (Durationinsecs as int)), 0), 108)  as [Minutes]  
from SampleData2
-- in group by removed durationinsecs
Group by Type, Created, Name

Why convert the date to a varchar? Leave the column as it should be, a date.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

The datatype for the Created column wasn't expressed in the test data that you nicely provided. I'm presuming that it's not actually a VARCHAR datatype in the following test data setup. Just for clarity in the code, I assigned datatypes to all of the columns.

 SELECT  Created        = CONVERT(DATE       ,d.Created)
        ,Name           = CONVERT(VARCHAR(10),d.Name)
        ,Type           = CONVERT(VARCHAR(10),d.Type)
        ,DurationInSecs = CONVERT(INT        ,d.DurationInSecs)
   INTO #SampleData2
   FROM (VALUES 
         ('2019-01-01','Dave'  ,'Break'   ,797)
        ,('2019-01-01','Dave'  ,'Break'   ,746)
        ,('2019-01-01','Dave'  ,'Break'   ,511)
        ,('2019-01-01','Bethan','Break'   ,631)
        ,('2019-01-01','Bethan','Break'   ,751)
        ,('2019-01-02','Bethan','Break'   ,400)
        ,('2019-01-02','Bethan','Break'   ,405)
        ,('2019-01-02','Bethan','Training',500)
        ,('2019-01-02','Bethan','Training',456)
        ) AS d (Created, Name, Type, DurationInSecs)
;

I'm also assuming that the order of the final output is important. You can't rely on the GROUP BY to always produce the correct order even if it appears to do so in this example. If you want to guarantee the correct order, you have to use an ORDER BY.

The following code formats the date as you indicated in your sample output and takes a little bit of a shortcut in formatting the Minutes column.

 SELECT  Created = CONVERT(CHAR(10),Created,103) --dd/mm/yyyy format
        ,Name
        ,Type
        ,Duration = SUM(DurationInSecs)
        ,Minutes  = CONVERT(TIME(0),DATEADD(ss,SUM(DurationInSecs),0))
   FROM #SampleData2
  GROUP BY Created, Name, Type
  ORDER BY Created, Name, Type
;

Using the given test data, the output looks like this:

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

craig.jenkins SSCommitted Points: 1643 More actions · Answer 5

craig.jenkins

SSCommitted

Points: 1643

July 23, 2019 at 1:54 pm

#3665388

Thanks for all your help guys