Combine subtotals from two group by rollup queries in SQL Server 2014

Question

I have a nested unionized query that's structured like below which is essentially two of the query structures from this solution: Using pivot table with column and row totals in sql server 2008

Is it possible to combine the two column subtotal rows between group by rollup queries?

SELECT
  [Line] = ISNULL(line_name, 'Total'),
  [A] = SUM([A]),
  [B] = SUM([B]),
  [C] = SUM([C]),
  Total = SUM([A]+[B]+[C])
FROM (
  SELECT sys_qty, line_name, stage_name
  FROM table1 a 
  INNER JOIN table2 b 
      ON a...=b...
  INNER JOIN table3 c 
      ON a...=c...
  WHERE line_name LIKE '%pattern1%'
) s1
PIVOT (
  COUNT(sys_qty)
  FOR stage_name IN ([A],[B],[C])
) p1
GROUP BY
  ROLLUP(line_name)
UNION ALL
SELECT
  [Line] = ISNULL(line_name, 'Total'),
  [A] = SUM([A]),
  [B] = SUM([B]),
  [C] = SUM([C]),
  Total = SUM([A]+[B]+[C])
FROM (
  SELECT sys_qty, line_name, stage_name
  FROM table1 a 
  INNER JOIN table2 b 
      ON a...=b...
  INNER JOIN table3 c 
      ON a...=c...
  INNER JOIN table4 d 
      ON b...=d...
  WHERE line_name LIKE '%pattern2%'
) s1
PIVOT (
  COUNT(sys_qty)
  FOR stage_name IN ([A],[B],[C])
) p2
GROUP BY
  ROLLUP(line_name)
;

Actual Results:

	A	B	C
p1.row1	a	b	c
p1.row2	d	e	f
Stage Total	a+d	b+e	c+f
p2.row1	g	h	i
Stage Total	g	h	i

Desired Results:

	A	B	C
p1.row1	a	b	c
p1.row2	d	e	f
p2.row1	g	h	i
Stage Total	a+d+g	b+e+h	c+f+i

---

Solution

Context: I'm trying to pivot the number of systems by stages (columns) and line (rows). One particular line needed an additional query involving an inner join with another table (table 4), but I still wanted to join all the lines together.

I made the following errors above:

- SELECT line_name, stage_name
+ SELECT sys_qty, line_name, stage_name

- COUNT(stage_name)
+ COUNT(sys_qty)

Solution:

SELECT
  [Line] = ISNULL(line_name, 'Total'),
  [A] = SUM([A]),
  [B] = SUM([B]),
  [C] = SUM([C]),
  Total = SUM([A]+[B]+[C])
FROM (
  SELECT sys_qty, line_name, stage_name
  FROM table1 a 
  INNER JOIN table2 b 
      ON a...=b...
  INNER JOIN table3 c 
      ON a...=c...
  WHERE line_name LIKE '%pattern1%'
  UNION ALL
  SELECT sys_qty, line_name, stage_name
  FROM table1 a 
  INNER JOIN table2 b 
      ON a...=b...
  INNER JOIN table3 c 
      ON a...=c...
  INNER JOIN table4 d 
      ON b...=d...
  WHERE line_name LIKE '%pattern2%'
) p1
PIVOT (
  COUNT(sys_qty)
  FOR stage_name IN ([A],[B],[C])
) pvt
GROUP BY
  ROLLUP(line_name)
;

Thank you Charlieface for the quick response as it was definitely the input I needed to get me out of that rabbit hole! I simply needed to restructure my query to union the raw data prior to pivoting.

Answer 1

Seems like you want to UNION ALL before pivoting, in a derived/nested SELECT, then grouping and pivoting that.

Having said that, it's probably easier to use COUNT(CASE rather than PIVOT anyway

SELECT
  Line = CASE WHEN GROUPING(line_name) = 1 THEN 'Total' ELSE line_name END,  -- deals with nulls
  A = COUNT(CASE WHEN stage_name = 'A' THEN 1 END),
  B = COUNT(CASE WHEN stage_name = 'B' THEN 1 END),
  C = COUNT(CASE WHEN stage_name = 'C' THEN 1 END),
  Total = COUNT(*)
FROM (
    SELECT line_name, stage_name
    FROM table1 a 
    INNER JOIN table2 b 
        ON a...=b...
    INNER JOIN table3 c 
        ON a...=c...
    WHERE line_name LIKE '%pattern1%'

    UNION ALL

    SELECT line_name, stage_name
    FROM table1 a 
    INNER JOIN table2 b 
        ON a...=b...
    INNER JOIN table3 c 
        ON a...=c...
    INNER JOIN table4 d 
        ON b...=d...
    WHERE line_name LIKE '%pattern2%'
) s

WHERE stage_name IN ('A', 'B', 'C')  -- do you need this also, to filter out other values?
        -- ... it might improve perf, also needed to get COUNT(*) to work correctly

GROUP BY
  ROLLUP(line_name)
;