I am making a view from the table [Data].[FCNP_Log] that has counts of several different attributes for several different 'Production Areas'. What I need is an additional row that says 'All' under the column 'Production Area' and has a total of all the other rows in each of the columns. I almost have what I need, but the following code doesn't quite give me what I need.
SELECT
ISNULL(total.[Production Area], 'All') AS [Production Area]
, COALESCE(total.[Total], 0) AS [Total]
, COALESCE(inprocess.[In Process], 0) AS [In Process]
, COALESCE(closed.[Closed], 0) AS [Closed]
, COALESCE(nofurtheraction.[No Further Action], 0) AS [No Further Action]
, COALESCE([60day].[60 Day], 0) AS [60 Day]
, COALESCE(dir.[DIR], 0) AS [DIR]
, COALESCE([8d].[8D], 0) AS [8D]
FROM
(SELECT
[DEPARTMENT] AS 'Production Area'
, COUNT([FCNP]) AS 'Total'
FROM [Data].[FCNP_Log]
GROUP BY [DEPARTMENT] WITH ROLLUP) total
FULL OUTER JOIN
(SELECT
[DEPARTMENT] AS 'Production Area'
, COUNT([FCNP]) AS 'In Process'
FROM [Data].[FCNP_Log]
WHERE [FCNPStatus] = 'In Process'
GROUP BY [DEPARTMENT] WITH ROLLUP) inprocess
ON
total.[Production Area] = inprocess.[Production Area]
FULL OUTER JOIN
(SELECT
[DEPARTMENT] AS 'Production Area'
, COUNT(FCNP) AS 'Closed'
FROM [Data].[FCNP_Log]
WHERE [FCNPStatus] = 'Closed'
GROUP BY [DEPARTMENT] WITH ROLLUP) closed
ON
total.[Production Area] = closed.[Production Area]
FULL OUTER JOIN
(SELECT
[DEPARTMENT] AS 'Production Area'
, COUNT([FCNP]) AS 'No Further Action'
FROM [Data].[FCNP_Log]
WHERE [Long_TermCANoFurtherAction] = 1
GROUP BY [DEPARTMENT] WITH ROLLUP) nofurtheraction
ON
total.[Production Area] = nofurtheraction.[Production Area]
FULL OUTER JOIN
(SELECT
[DEPARTMENT] AS 'Production Area'
, COUNT([FCNP]) AS '60 Day'
FROM [Data].[FCNP_Log]
WHERE [Long_Term60Day] = 1
GROUP BY [DEPARTMENT] WITH ROLLUP) [60day]
ON
total.[Production Area] = [60day].[Production Area]
FULL OUTER JOIN
(SELECT
[DEPARTMENT] AS 'Production Area'
, COUNT([FCNP]) AS 'DIR'
FROM [Data].[FCNP_Log]
WHERE [Long_TermCADIR] = 1
GROUP BY [DEPARTMENT] WITH ROLLUP) dir
ON
total.[Production Area] = dir.[Production Area]
FULL OUTER JOIN
(SELECT
[DEPARTMENT] AS 'Production Area'
, COUNT([FCNP]) AS '8D'
FROM [Data].[FCNP_Log]
WHERE [Long_TermCA8D] = 1
GROUP BY [DEPARTMENT] WITH ROLLUP) [8d]
ON
total.[Production Area] = [8d].[Production Area]
This code gives me the following table: https://i.sstatic.net/sxbeW.png
As you can see, the ROLLUPs that I have are adding up the columns correctly, but they are staggered, and I need all the 'All' rows to be combined into a single row.
I've tried SELECTing the whole block of code as "t1" and adding an additional GROUP BY t1.[Production Area] WITH ROLLUP at the end, but it's giving me errors.
I feel like there is probably a simple solution but I can't figure it out. Any help is appreciated.
EDIT: I did figure out a way to get the results I wanted. I removed all the WITH ROLLUP and put WITH CountTable AS ( before the code above, and the following code afterwards:
)
SELECT * FROM CountTable
UNION
SELECT
'All' AS [Production Area]
, SUM(CountTable.[Total]) AS [Total]
, SUM(CountTable.[In Process]) AS [In Process]
, SUM(CountTable.[Closed]) AS [Closed]
, SUM(CountTable.[No Further Action]) AS [No Further Action]
, SUM(CountTable.[60 Day]) AS [60 Day]
, SUM(CountTable.[DIR]) AS [DIR]
, SUM(CountTable.[8D]) AS [8D]
FROM CountTable
EDIT: I did figure out a way to get the results I wanted. I removed all the WITH ROLLUP and put WITH CountTable AS ( before the code above, and the following code afterwards:
)
SELECT * FROM CountTable
UNION
SELECT
'All' AS [Production Area]
, SUM(CountTable.[Total]) AS [Total]
, SUM(CountTable.[In Process]) AS [In Process]
, SUM(CountTable.[Closed]) AS [Closed]
, SUM(CountTable.[No Further Action]) AS [No Further Action]
, SUM(CountTable.[60 Day]) AS [60 Day]
, SUM(CountTable.[DIR]) AS [DIR]
, SUM(CountTable.[8D]) AS [8D]
FROM CountTable