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: 246959 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Thom A SSC Guru Points: 99129 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: 1004318 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