Need a total for each column and to show 0 for null data points

Question

I have a script (below) that has multiple joined columns that I need a final row at the bottom with the total sum of each column. I know of the sum function but I cannot figure out where to put it within the script. I would also like to know how or where to incorporate this statement SELECT ISNULL(myColumn, 0 ) FROM myTable so the nulls show 0.

SELECT A.[start_tran_hour],
       [singles picked],
       [single packed],
       [multis picked],
       [units sorted],
       [multis packed],
       [sa packed],
       [sa picked],
       [total picked],
       [total packed],
       [total shipped]
FROM   (SELECT Datepart(hour, start_tran_time)start_tran_hour,
               Sum(tran_qty)                  AS 'Total Shipped'
        FROM   t_tran_log WITH(nolock)
        WHERE  tran_type IN ( '340', '341' )
               AND Cast(start_tran_date AS DATE) = '2021-07-06'
        GROUP  BY Datepart(hour, start_tran_time))A
       LEFT JOIN (SELECT Datepart(hour, start_tran_time)start_tran_hour,
                         Sum(tran_qty)                  AS 'Total Picked'
                  FROM   t_tran_log WITH(nolock)
                  WHERE  tran_type = '301'
                         AND Cast(start_tran_date AS DATE) = '2021-07-06'
                  GROUP  BY Datepart(hour, start_tran_time))B
              ON A.start_tran_hour = B.start_tran_hour
       LEFT JOIN (SELECT Datepart(hour, start_tran_time)start_tran_hour,
                         Sum(tran_qty)                  AS 'Single Packed'
                  FROM   t_tran_log WITH(nolock)
                  WHERE  tran_type = '315'
                         AND description = 'Single Packing'
                         AND Cast(start_tran_date AS DATE) = '2021-07-06'
                  GROUP  BY Datepart(hour, start_tran_time))C
              ON A.start_tran_hour = C.start_tran_hour
       LEFT JOIN (SELECT Datepart(hour, start_tran_time)start_tran_hour,
                         Sum(tran_qty)                  AS 'Singles Picked'
                  FROM   t_tran_log WITH(nolock)
                         LEFT JOIN t_order WITH(nolock)
                                ON
                         t_tran_log.control_number = t_order.order_number
                  WHERE  tran_type = '301'
                         AND t_order.route = 'SINGLE'
                         AND Cast(start_tran_date AS DATE) = '2021-07-06'
                  GROUP  BY Datepart(hour, start_tran_time))D
              ON A.start_tran_hour = D.start_tran_hour
       LEFT JOIN (SELECT Datepart(hour, start_tran_time)start_tran_hour,
                         Sum(tran_qty)                  AS 'Multis Picked'
                  FROM   t_tran_log WITH(nolock)
                         LEFT JOIN t_order WITH(nolock)
                                ON
                         t_tran_log.control_number = t_order.order_number
                  WHERE  tran_type = '301'
                         AND t_order.route = 'MULTI'
                         AND Cast(start_tran_date AS DATE) = '2021-07-06'
                  GROUP  BY Datepart(hour, start_tran_time))E
              ON A.start_tran_hour = E.start_tran_hour
       LEFT JOIN (SELECT Datepart(hour, start_tran_time)start_tran_hour,
                         Sum(tran_qty)                  AS 'Multis Packed'
                  FROM   t_tran_log WITH(nolock)
                  WHERE  tran_type = '315'
                         AND description = 'Multi Packing'
                         AND Cast(start_tran_date AS DATE) = '2021-07-06'
                  GROUP  BY Datepart(hour, start_tran_time))F
              ON A.start_tran_hour = F.start_tran_hour
       LEFT JOIN (SELECT Datepart(hour, start_tran_time)start_tran_hour,
                         Sum(tran_qty)                  AS 'SA Picked'
                  FROM   t_tran_log WITH(nolock)
                  WHERE  tran_type = '301'
                         AND ( location_id LIKE 'PR%'
                                OR location_id LIKE 'SA%' )
                         AND Cast(start_tran_date AS DATE) = '2021-07-06'
                  GROUP  BY Datepart(hour, start_tran_time))G
              ON A.start_tran_hour = G.start_tran_hour
       LEFT JOIN (SELECT Datepart(hour, start_tran_time)start_tran_hour,
                         Sum(tran_qty)                  AS 'SA Packed'
                  FROM   t_tran_log WITH(nolock)
                  WHERE  tran_type = '315'
                         AND ( location_id LIKE 'PACKSA%' )
                         AND Cast(start_tran_date AS DATE) = '2021-07-06'
                  GROUP  BY Datepart(hour, start_tran_time))H
              ON A.start_tran_hour = H.start_tran_hour
       LEFT JOIN (SELECT Datepart(hour, start_tran_time)start_tran_hour,
                         Sum(tran_qty)                  AS 'Units Sorted'
                  FROM   t_tran_log WITH(nolock)
                  WHERE  tran_type = '311'
                         AND Cast(start_tran_date AS DATE) = '2021-07-06'
                  GROUP  BY Datepart(hour, start_tran_time))I
              ON A.start_tran_hour = I.start_tran_hour
       LEFT JOIN (SELECT Datepart(hour, start_tran_time)start_tran_hour,
                         Sum(tran_qty)                  AS 'Total Packed'
                  FROM   t_tran_log WITH(nolock)
                  WHERE  tran_type = '315'
                         AND Cast(start_tran_date AS DATE) = '2021-07-06'
                  GROUP  BY ( Datepart(hour, start_tran_time) ))J
              ON A.start_tran_hour = J.start_tran_hour
ORDER  BY A.start_tran_hour 

Answer 1

I assume that you have some hours where every measure value in a column is null (or there just aren't any) so SUM(all_null_values) = NULL and you want it to be 0.

This will take care of the nulls and add a total row.

SELECT A.[start_tran_hour],
, sum(coalesce([singles picked], 0)) as 'singles picked'
, sum(coalesce([single packed],  0)) as 'single packed'
, sum(coalesce([multis picked],  0)) as 'multis picked'
, sum(coalesce([units sorted],   0)) as 'units sorted'
, sum(coalesce([multis packed],  0)) as 'multis packed'
, sum(coalesce([sa packed],      0)) as 'sa packed'
, sum(coalesce([sa picked],      0)) as 'sa picked'
, sum(coalesce([total picked],   0)) as 'total picked'
, sum(coalesce([total packed],   0)) as 'total packed'
, sum(coalesce([total shipped],  0)) as 'total shipped'

FROM   (
  SELECT Datepart(hour, start_tran_time) as start_tran_hour
  , Sum(tran_qty) AS 'Total Shipped'
  FROM   t_tran_log WITH(nolock)
  WHERE  tran_type IN ( '340', '341' )
    AND Cast(start_tran_date AS DATE) = '2021-07-06'
  GROUP  BY Datepart(hour, start_tran_time)
)A
LEFT JOIN (
  SELECT Datepart(hour, start_tran_time) as start_tran_hour
  , Sum(tran_qty) AS 'Total Picked'
  FROM   t_tran_log WITH(nolock)
  WHERE  tran_type = '301'
    AND Cast(start_tran_date AS DATE) = '2021-07-06'
  GROUP  BY Datepart(hour, start_tran_time)
)B ON A.start_tran_hour = B.start_tran_hour
LEFT JOIN (
  SELECT Datepart(hour, start_tran_time) as start_tran_hour
  , Sum(tran_qty) AS 'Single Packed'
  FROM   t_tran_log WITH(nolock)
  WHERE  tran_type = '315'
    AND description = 'Single Packing'
    AND Cast(start_tran_date AS DATE) = '2021-07-06'
  GROUP  BY Datepart(hour, start_tran_time)
)C ON A.start_tran_hour = C.start_tran_hour
LEFT JOIN (
  SELECT Datepart(hour, start_tran_time) as start_tran_hour
  , Sum(tran_qty) AS 'Singles Picked'
  FROM   t_tran_log WITH(nolock)
    LEFT JOIN t_order WITH(nolock) ON t_tran_log.control_number = t_order.order_number
  WHERE  tran_type = '301'
    AND t_order.route = 'SINGLE'
    AND Cast(start_tran_date AS DATE) = '2021-07-06'
  GROUP  BY Datepart(hour, start_tran_time)
)D ON A.start_tran_hour = D.start_tran_hour
LEFT JOIN (
  SELECT Datepart(hour, start_tran_time) as start_tran_hour
  , Sum(tran_qty) AS 'Multis Picked'
  FROM   t_tran_log WITH(nolock)
    LEFT JOIN t_order WITH(nolock) ON t_tran_log.control_number = t_order.order_number
  WHERE  tran_type = '301'
    AND t_order.route = 'MULTI'
    AND Cast(start_tran_date AS DATE) = '2021-07-06'
  GROUP  BY Datepart(hour, start_tran_time)
)E ON A.start_tran_hour = E.start_tran_hour
LEFT JOIN (
  SELECT Datepart(hour, start_tran_time) as start_tran_hour
  , Sum(tran_qty) AS 'Multis Packed'
  FROM   t_tran_log WITH(nolock)
  WHERE  tran_type = '315'
    AND description = 'Multi Packing'
    AND Cast(start_tran_date AS DATE) = '2021-07-06'
  GROUP  BY Datepart(hour, start_tran_time)
)F ON A.start_tran_hour = F.start_tran_hour
LEFT JOIN (
  SELECT Datepart(hour, start_tran_time) as start_tran_hour
  , Sum(tran_qty) AS 'SA Picked'
  FROM   t_tran_log WITH(nolock)
  WHERE  tran_type = '301'
    AND ( location_id LIKE 'PR%'
       OR location_id LIKE 'SA%' )
    AND Cast(start_tran_date AS DATE) = '2021-07-06'
  GROUP  BY Datepart(hour, start_tran_time)
) G ON A.start_tran_hour = G.start_tran_hour
LEFT JOIN (
  SELECT Datepart(hour, start_tran_time) as start_tran_hour
  , Sum(tran_qty) AS 'SA Packed'
  FROM   t_tran_log WITH(nolock)
  WHERE  tran_type = '315'
    AND ( location_id LIKE 'PACKSA%' )
    AND Cast(start_tran_date AS DATE) = '2021-07-06'
  GROUP  BY Datepart(hour, start_tran_time)
) H ON A.start_tran_hour = H.start_tran_hour
LEFT JOIN (
  SELECT Datepart(hour, start_tran_time) as start_tran_hour
  , Sum(tran_qty) AS 'Units Sorted'
  FROM   t_tran_log WITH(nolock)
  WHERE  tran_type = '311'
    AND Cast(start_tran_date AS DATE) = '2021-07-06'
  GROUP  BY Datepart(hour, start_tran_time)
) I ON A.start_tran_hour = I.start_tran_hour
LEFT JOIN (
  SELECT Datepart(hour, start_tran_time) as start_tran_hour
  , Sum(tran_qty) AS 'Total Packed'
  FROM   t_tran_log WITH(nolock)
  WHERE  tran_type = '315'
    AND Cast(start_tran_date AS DATE) = '2021-07-06'
  GROUP  BY ( Datepart(hour, start_tran_time) )
) J ON A.start_tran_hour = J.start_tran_hour

group by rollup (A.start_tran_hour)

ORDER  BY A.start_tran_hour 

However, that still contains the LEFT INNER JOIN (Not a real thing. I made up that term.) from your original query.

FROM  t_tran_log
  LEFT JOIN t_order ON t_tran_log.control_number = t_order.order_number
WHERE tran_type = '301'
    AND t_order.route = 'MULTI'

Including t_order.route = 'MULTI' in the WHERE clause causes the LEFT JOIN to be an INNER JOIN. So we'll want to fix that.

But John Cappelletti's suggestion is also good. Let's try referencing the table once in the FROM clause and computing each column in the SELECT clause.

SELECT Datepart(hour, start_tran_time) as start_tran_hour,
, sum(
  case
    when tran_type = '301'
      AND t_order.route = 'SINGLE'
      then [singles picked]
    else 0
  end) as 'singles picked'
, sum(
  case
    when tran_type = '315'
      AND description = 'Single Packing'
      then [single packed]
    else 0
  end) as 'single packed'
, sum(
  case
    when tran_type = '301'
      AND t_order.route = 'MULTI'
      then [multis picked]
    else 0
  end) as 'multis picked'
, sum(
  case
    when tran_type = '311'
      then [units sorted]
    else 0
  end) as 'units sorted'
, sum(
  case
    when tran_type = '315'
      AND description = 'Multi Packing'
      then [multis packed]
    else 0
  end) as 'multis packed'
, sum(
  case
    when tran_type = '315'
      AND location_id LIKE 'PACKSA%'
      then [sa packed]
    else 0
  end) as 'sa packed'
, sum(
  case
    when tran_type = '301'
      AND ( location_id LIKE 'PR%'
         OR location_id LIKE 'SA%' )
      then [sa picked]
    else 0
  end) as 'sa picked'
, sum(
  case
    when tran_type = '301'
      then [total picked]
    else 0
  end) as 'total picked'
, sum(
  case
    when tran_type = '315'
      then [total packed]
    else 0
  end) as 'total packed'
, sum(
  case
    when tran_type IN ( '340', '341' )
      then [total shipped]
    else 0
  end) as 'total shipped'

from t_tran_log WITH(nolock)
  LEFT JOIN t_order WITH(nolock) ON t_tran_log.control_number = t_order.order_number
                                AND t_order.route in ('SINGLE', 'MULTI')

where Cast(start_tran_date AS DATE) = '2021-07-06'
  and tran_type IN ('340', '341', '301', '315', '311')

group by rollup (Datepart(hour, start_tran_time))

ORDER BY Datepart(hour, start_tran_time)

Without a database schema and sample data, I can't be sure that any of that will work. Next time, create and reference a dbfiddle.

Answer 2

Apologies for the delay. I kept getting tripped up over Picked and Packed.

Perhaps this will get you started with a reduced conditional aggregation

You may notice I commented out [singles picked] and [multis picked]. It looks like a JOIN is required and I don't know enough about your data to make an assumption.

Select start_tran_hour = Datepart(hour, start_tran_time)
      --,[singles picked]  
      ,[single packed] = sum( case when tran_type in ('315') 
                                    and description = 'Single Packing'      then tran_qty else 0 end)
      --,[multis picked]
      ,[units sorted]  = sum( case when tran_type in ('311')                then tran_qty else 0 end)
      ,[multis packed] = sum( case when tran_type in ('315') 
                                    and description='Multi Packing'         then tran_qty else 0 end)
      ,[sa packed]     = sum( case when tran_type in ('315') 
                                    and location_id LIKE 'PACKSA%'          then tran_qty else 0 end)
      ,[sa picked]     = sum( case when tran_type in ('301') 
                                    and left(location_id,2) in ('PR','SA')  then tran_qty else 0 end)
      ,[Total Picked]  = sum( case when tran_type in ('301')                then tran_qty else 0 end)
      ,[Total Packed]  = sum( case when tran_type in ('315')                then tran_qty else 0 end)
      ,[Total Shipped] = sum( case when tran_type in ('340','341')          then tran_qty else 0 end)
 From  t_tran_log
 Where Cast(start_tran_date AS DATE) = '2021-07-06'
 Group By Grouping Sets ( 
                         (Datepart(hour, start_tran_time))
                        ,()
                        )