When using SQL Server UNION ALL, can I merge columns? -

When using SQL Server UNION ALL, can I merge columns? -

i have 2 tables in sql server.

table 1: jbin : list of invoices. table 2: arth : list of invoices approved.

i have created query displays info on number of days invoice overdue by.

the results table looks this:

+-----------------------+--------------+--------------+------+ | overduerange | overduecount | total amount | invo | +-----------------------+--------------+--------------+------+ | due in 0-7 days | 1 | 180215.61 | -1 | | due in 31-45 days | 1 | 153356.10 | -32 | | overdue 46+ days | 1 | 125167.34 | 47 | | due in 0-7 days | 25 | 2708613.25 | -1 | | due in 31-45 days | 3 | 62312.51 | -32 | | overdue 15-30 days | 12 | 295140.15 | 16 | | overdue 1-7 days | 1 | 111070.25 | 2 | | overdue 31-45 days | 1 | 2150.50 | 32 | | overdue 46+ days | 8 | 546907.75 | 47 | | overdue 8-14 days | 1 | 156985.68 | 9 | +-----------------------+--------------+--------------+------+

in query, have union all statement.

the first 3 rows of table jbin whereas rest arth.

is possible, when using union all combine data. example, instead of having 2 rows called "due in 0-7 days, have 1 single row sums overduecount , total amount fields.

e.g.

instead of this:

+-----------------+--------------+--------------+------+ | overduerange | overduecount | total amount | invo | +-----------------+--------------+--------------+------+ | due in 0-7 days | 1 | 180215.61 | -1 | | due in 0-7 days | 25 | 2708613.25 | -1 | +-----------------+--------------+--------------+------+

i want this:

+-----------------+--------------+--------------+------+ | overduerange | overduecount | total amount | invo | +-----------------+--------------+--------------+------+ | due in 0-7 days | 26 | 2888828.86 | -1 | +-----------------+--------------+--------------+------+

fyi:

this query:

with temp ([invdescription], [invoiceamount], [overdueby], [datepaid]) (select invdescription, invtotal invoiceamount, (datediff(day,duedate,getdate())) overdueby, null datepaid jbin invstatus <> 'i' , jbco = 1 , invtotal > 0 ), temp2 ([invdescription], [invoiceamount], [overdueby], [datepaid]) (select description, sum(invoiced) invoiceamount, (datediff(day,duedate,getdate())) overdueby, payfulldate datepaid arth arco = 1 , invoiced > 0 grouping description, duedate, payfulldate, invoice ) select case when overdueby >= 46 'overdue 46+ days' when overdueby >= 31 , overdueby <= 45 'overdue 31-45 days' when overdueby >= 15 , overdueby <= 30 'overdue 15-30 days' when overdueby >= 8 , overdueby <= 14 'overdue 8-14 days' when overdueby >= 1 , overdueby <= 7 'overdue 1-7 days' when overdueby <= 0 , overdueby >= -7 'due in 0-7 days' when overdueby <= -8 , overdueby >= -14 'due in 8-14 days' when overdueby <= -15 , overdueby >= -30 'due in 15-30 days' when overdueby <= -31 , overdueby >= -45 'due in 31-45 days' when overdueby <= -46 'due in 46+ days' else 'less that' end overduerange, count(*) overduecount, sum(temp.invoiceamount) [total amount], case when overdueby >= 46 '47' when overdueby >= 31 , overdueby <= 45 '32' when overdueby >= 15 , overdueby <= 30 '16' when overdueby >= 8 , overdueby <= 14 '9' when overdueby >= 1 , overdueby <= 7 '2' when overdueby <= 0 , overdueby >= -7 '-1' when overdueby <= -8 , overdueby >= -14 '-9' when overdueby <= -15 , overdueby >= -30 '-16' when overdueby <= -31 , overdueby >= -45 '-32' when overdueby <= -46 '-47' else 'error' end [invo] temp grouping case when overdueby >= 46 'overdue 46+ days' when overdueby >= 31 , overdueby <= 45 'overdue 31-45 days' when overdueby >= 15 , overdueby <= 30 'overdue 15-30 days' when overdueby >= 8 , overdueby <= 14 'overdue 8-14 days' when overdueby >= 1 , overdueby <= 7 'overdue 1-7 days' when overdueby <= 0 , overdueby >= -7 'due in 0-7 days' when overdueby <= -8 , overdueby >= -14 'due in 8-14 days' when overdueby <= -15 , overdueby >= -30 'due in 15-30 days' when overdueby <= -31 , overdueby >= -45 'due in 31-45 days' when overdueby <= -46 'due in 46+ days' else 'less that' end, case when overdueby >= 46 '47' when overdueby >= 31 , overdueby <= 45 '32' when overdueby >= 15 , overdueby <= 30 '16' when overdueby >= 8 , overdueby <= 14 '9' when overdueby >= 1 , overdueby <= 7 '2' when overdueby <= 0 , overdueby >= -7 '-1' when overdueby <= -8 , overdueby >= -14 '-9' when overdueby <= -15 , overdueby >= -30 '-16' when overdueby <= -31 , overdueby >= -45 '-32' when overdueby <= -46 '-47' else 'error' end union select case when overdueby >= 46 'overdue 46+ days' when overdueby >= 31 , overdueby <= 45 'overdue 31-45 days' when overdueby >= 15 , overdueby <= 30 'overdue 15-30 days' when overdueby >= 8 , overdueby <= 14 'overdue 8-14 days' when overdueby >= 1 , overdueby <= 7 'overdue 1-7 days' when overdueby <= 0 , overdueby >= -7 'due in 0-7 days' when overdueby <= -8 , overdueby >= -14 'due in 8-14 days' when overdueby <= -15 , overdueby >= -30 'due in 15-30 days' when overdueby <= -31 , overdueby >= -45 'due in 31-45 days' when overdueby <= -46 'due in 46+ days' else 'less that' end overduerange, count(*) overduecount, sum(temp2.invoiceamount) [total amount], case when overdueby >= 46 '47' when overdueby >= 31 , overdueby <= 45 '32' when overdueby >= 15 , overdueby <= 30 '16' when overdueby >= 8 , overdueby <= 14 '9' when overdueby >= 1 , overdueby <= 7 '2' when overdueby <= 0 , overdueby >= -7 '-1' when overdueby <= -8 , overdueby >= -14 '-9' when overdueby <= -15 , overdueby >= -30 '-16' when overdueby <= -31 , overdueby >= -45 '-32' when overdueby <= -46 '-47' else 'error' end [invo] temp2 temp2.datepaid null grouping case when overdueby >= 46 'overdue 46+ days' when overdueby >= 31 , overdueby <= 45 'overdue 31-45 days' when overdueby >= 15 , overdueby <= 30 'overdue 15-30 days' when overdueby >= 8 , overdueby <= 14 'overdue 8-14 days' when overdueby >= 1 , overdueby <= 7 'overdue 1-7 days' when overdueby <= 0 , overdueby >= -7 'due in 0-7 days' when overdueby <= -8 , overdueby >= -14 'due in 8-14 days' when overdueby <= -15 , overdueby >= -30 'due in 15-30 days' when overdueby <= -31 , overdueby >= -45 'due in 31-45 days' when overdueby <= -46 'due in 46+ days' else 'less that' end, case when overdueby >= 46 '47' when overdueby >= 31 , overdueby <= 45 '32' when overdueby >= 15 , overdueby <= 30 '16' when overdueby >= 8 , overdueby <= 14 '9' when overdueby >= 1 , overdueby <= 7 '2' when overdueby <= 0 , overdueby >= -7 '-1' when overdueby <= -8 , overdueby >= -14 '-9' when overdueby <= -15 , overdueby >= -30 '-16' when overdueby <= -31 , overdueby >= -45 '-32' when overdueby <= -46 '-47' else 'error' end

you can create derived table union query. this:

select somefields, sum(something) thesum ( union query goes here ) derivedtable grouping somefields

sql sql-server sum union-all