SQL Server (2008) UNION with ROLL UP

Question

I have an expense report app that collects expense charges and account numbers into one table but a given charges can be split with some portion charged to one GL account and the remainder charged to a different account. The accommodate the cost splitting, the Expense table has two pairs of amount and Account number columns that are used by the data entry process. Like this:

create table Expenses (expheaderid int, explineid int, amount_split1 decimal(5,2), 
account int, amount_split2 decimal(5,2), account2 int);
insert Expenses values (57, 11, 47.35, 80400, 0, 0);
insert Expenses values (57, 12, 163.31, 80440, 0, 0);
insert Expenses values (57, 13, 30, 80401, 5.90, 70410);
insert Expenses values (57, 14, 35, 80440, 0, 0);
insert Expenses values (57, 15, 45.15, 80440, 0, 0);
insert Expenses values (57, 16, 145.87, 80400, 68.14, 80400);
insert Expenses values (57, 17, 67.35, 80870, 0, 0);
insert Expenses values (57, 18, 105, 80402, 34.50, 80440);

I need to roll up (GROUP BY) the amounts from the same account codes but the roll up has to occur between both pairs of amounts and Account number columns - either (amount_split1 & account) 1st pair or the (amount_split2 & account 2) 2nd column pair. So my table looks like this:

expheaderid explineid amount_split1 account amount_split2 account2
----------- --------- ------------- ------- ------------- --------
         57        11         47.35   80400           0.00       0 
         57        12        163.31   80440           0.00       0
         57        13         30.00   80401           5.90   70410
         57        14         35.00   80440           0.00       0
         57        15         45.15   80440           0.00       0
         57        16        145.87   80400          68.14   80400
         57        17         67.35   80870              0       0
         57        18        105.00   80402          34.50   80440

I want to UNION the amount_split1 costs with the amount_split2 costs GROUPing them by account number.

The result should look like this, collecting from GL accounts in both the account column and the account2 column

AggTotal  GLAccount
--------  ---------
   261.36     80400
    30.00     80401
   105.00     80402
   277.96     80440
    67.35     80470
and probably...
     0.00         0   -which I don't care about.

I'm not very good at compound queries at all. I'm trying to UNION and GROUP by in one statement like this:

select sum(AggTotal), GLAccount from 
((select amount_split1 as AggTotal, account as GLAccount from Expenses)
union all 
(select amount_split2 as AggTotal, account2 as GLAccount from Expenses))as t 
where Expenses.expheaderid=57 group by GLAccount;

But the expheaderid column will not bind. This data is part of a larger dataset so the WHERE clause on expheaderid has to work.

Thanks for any help.

As to why it will not bind: your from clause consists of 1 table `t` so when the where clause executes, it has no knowledge of an `Expenses` table. Additionally table `t` does not have an `expheaderid` column; so even if we did `t.expheaderid` it wouldn't be found. So where do you expect the SQL engine to find `Expenses.expheaderid`? Either include it in the selects with the unions or filter by it individually in a select. — xQbert, Oct 04 '18 at 19:36

score 2 · Accepted Answer · answered Oct 04 '18 at 19:21

You can try to UNION ALL in a subquery, then add expheaderid columns because you need to use it on where in subquery final do SUM.

Query 1:

SELECT SUM(AggTotal) AggTotal,GLAccount
FROM (
  select amount_split1 as AggTotal, 
         account as GLAccount,
         expheaderid  --  <-- add this column
  from Expenses
  union all 
  select 
      amount_split2 as AggTotal, 
      account2 as GLAccount ,
      expheaderid
  from Expenses
) t1
where expheaderid = 57
GROUP BY GLAccount

Results:

| AggTotal | GLAccount |
|----------|-----------|
|        0 |         0 |
|      5.9 |     70410 |
|   261.36 |     80400 |
|       30 |     80401 |
|      105 |     80402 |
|   277.96 |     80440 |
|    67.35 |     80870 |

score 1 · Answer 2 · answered Oct 04 '18 at 19:09

Did you tried do it in parts. Try a CTE like this one

WITH C AS(
    (select amount_split1 as AggTotal, account as GLAccount,expheaderid from Expenses)
    union all 
    (select amount_split2 as AggTotal, account2 as GLAccount,expheaderid from Expenses) 
)SELECT  sum(AggTotal) AS SUMAVGTOTAL, GLAccount  
FROM C
WHERE expheaderid=57
GROUP BY GLAccount

Or this one

   select sum(AggTotal), GLAccount from 
    ((select amount_split1 as AggTotal, account as GLAccount from Expenses   where expheaderid=57)
    union all 
    (select amount_split2 as AggTotal, account2 as GLAccount from Expenses where expheaderid=57))as t 
     group by GLAccount;

Adding your expheaderid=57 into each part of union

Also you can use a temptable ## to do the same. Tell me if it does not work

SQL Server (2008) UNION with ROLL UP

2 Answers2