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