locked
Help with SubTotals RRS feed

  • Question

  • User-373571878 posted

    Hello,

    Is it possible to get a subtotal after each grouped ActDate?

    Here is query:

    Declare @FromDate as date
    Declare @ToDate date
    Select @FromDate = '2019-04-30'
    select @ToDate = '2019-05-01'

    Select ActDate, Region,
    Crews, Num_Act
    from
    (select
    distinct(substring(crewid,1,3)) Region,
    Count(distinct(Crewid)) Crews,
    Count(distinct(EmployeeId)) Num_Act,
    cast(ts.time as date) ActDate
    from Timesheet TS, Employee EP
    where TS.EmployeeId = EP.id
    and EP.IsActive = 1
    and (cast(ts.time as date) between @FromDate and @ToDate)
    group by (cast(ts.time as date)),(substring(crewid,1,3))) a
    Group by Actdate, region,crews,num_Act
    order by ActDate, Region

    Here is current results:

    ActDate..........Region...Crews....Num_Act
    2019-04-30.....021..........1..............1
    2019-04-30.....023..........1...............2
    2019-04-30.....092.........19............24
    2019-05-01.....021..........1...............1
    2019-05-01.....023..........1...............2
    2019-05-01.....092..........19...........24

    Looking for:

    ActDate..........Region...Crews....Num_Act
    2019-04-30.....021..........1..............1
    2019-04-30.....023..........1...............2
    2019-04-30.....092.........19............24
    Subtotal.............................21...........27
    2019-05-01.....021..........1...............1
    2019-05-01.....023..........1...............2
    2019-05-01.....092..........19...........24
    Subtotal.............................21...........27
    Total:...................................42...........54       

    Thanks

    Thursday, May 2, 2019 2:49 PM

Answers

  • User77042963 posted

    Plug your own aggregate function query into this sample.

    We are using grouping sets in newer versions now.

    CREATE TABLE mytable(
       ActDate DATE   
      ,Region  Varchar(3)   
      ,Crews   INTEGER   
      ,Num_Act INTEGER   
    );
    INSERT INTO mytable(ActDate,Region,Crews,Num_Act) VALUES
     ('2019-04-30','021',1,1)
    ,('2019-04-30','023',1,2)
    ,('2019-04-30','092',19,24)
    ,('2019-05-01','021',1,1)
    ,('2019-05-01','023',1,2)
    ,('2019-05-01','092',19,24);
    
    
    select Case when Region is null and Format(ActDate,'yyyy-MM-dd') is not null then 'Subtotal'
       when Region is null and Format(ActDate,'yyyy-MM-dd') is  null then 'Total'
       Else Format(ActDate,'yyyy-MM-dd') end ActDate 
     , ISNULL(Region,'') Region, 
     SUM(Crews) Crews, SUM(Num_Act) Num_Act
    from mytable
    Group by 
     GROUPING SETS (( ActDate,Region),( ActDate),()) ;
     
    
    drop table mytable


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 2, 2019 7:43 PM

All replies

  • User-373571878 posted

    I am almost there: using these enhancements:

    Select case when Region is null then null else ActDate end ActDate,
    case when Region is null then 'SubTotal:' else Region end Region,
    Sum(Crews) Crews, Sum(Num_Act) ActiveToday
    from
    (select
    distinct(substring(crewid,1,3)) Region,
    Count(distinct(Crewid)) Crews,
    Count(distinct(EmployeeId)) Num_Act,
    cast(ts.time as date) ActDate

    from Timesheet TS, Employee EP
    where TS.EmployeeId = EP.id
    and EP.IsActive = 1
    and (cast(ts.time as date) between @FromDate and @ToDate)
    group by (cast(ts.time as date)),(substring(crewid,1,3))) a
    Group by Actdate, region with rollup

    However instead of stating GrandTotal at the end it uses the SubTotal Text, have to figure out how to put in Grand Total, or leave the grand total out all together.

    ActDate.........Region........ Crews......... ActiveToday
    2019-04-30.... 021..................1...................... 1
    2019-04-30.....023...................1.......................2
    2019-04-30.... 092...................19................. 24
    NULL............. SubTotal:..........21....................27
    2019-05-01....021....................1.......................1
    2019-05-01....023.....................1......................2
    2019-05-01....092......................19................. 24
    NULL..............SubTotal:............21...................27
    NULL............. SubTotal:............42..................54

    Thursday, May 2, 2019 5:46 PM
  • User77042963 posted

    Plug your own aggregate function query into this sample.

    We are using grouping sets in newer versions now.

    CREATE TABLE mytable(
       ActDate DATE   
      ,Region  Varchar(3)   
      ,Crews   INTEGER   
      ,Num_Act INTEGER   
    );
    INSERT INTO mytable(ActDate,Region,Crews,Num_Act) VALUES
     ('2019-04-30','021',1,1)
    ,('2019-04-30','023',1,2)
    ,('2019-04-30','092',19,24)
    ,('2019-05-01','021',1,1)
    ,('2019-05-01','023',1,2)
    ,('2019-05-01','092',19,24);
    
    
    select Case when Region is null and Format(ActDate,'yyyy-MM-dd') is not null then 'Subtotal'
       when Region is null and Format(ActDate,'yyyy-MM-dd') is  null then 'Total'
       Else Format(ActDate,'yyyy-MM-dd') end ActDate 
     , ISNULL(Region,'') Region, 
     SUM(Crews) Crews, SUM(Num_Act) Num_Act
    from mytable
    Group by 
     GROUPING SETS (( ActDate,Region),( ActDate),()) ;
     
    
    drop table mytable


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 2, 2019 7:43 PM
  • User-373571878 posted

    wow limno, that worked!

    I got close when I tried the coalesce function, but not quit there. You hit the nail on the head!

    Thank you for your time.

    Thursday, May 2, 2019 7:55 PM