combine SELECTS in ONE VIEW DISPLAY

Question

I need to know of a way to combine multiple SELECT statements in one VIEW? I tried the UNION ALL, but it fails since I am using unique columns to aggregate the GRAND TOTAL.
I am a student this is part of a group project.

I have one table with 4 columns: account, description, short_description, and balance. The COA (chart of accounts) is an excel spreadsheet that is imported.

CREATE VIEW [account_balance_sums]
AS
  SELECT SUM(balance) AS total,   
  SUBSTRING (Account,0,2) AS account_group
    FROM COA
    GROUP BY account_group
GO


 SELECT * FROM [account_balance_sums]
 SELECT SUM(total) AS Grand_total
 FROM [account_balance_sums]

Answer 1

In a UNION'd statement, there must be:

1. The same number of columns in each SELECT statement
2. The data types must match at each position in the SELECT statement

Use:

SELECT * 
  FROM [account_balance_sums]
UNION ALL
SELECT SUM(total),
       NULL AS account_group
  FROM [account_balance_sums]

Answer 2

UNION ALL should work. basic structure like this

select a,b,c,d 
from t1
union all 
select a,b,c,e
from t2

so long as d and e are the same data type.

to do the sum, then you wrap this with the aggregation layer - using this structure as an inline view (among other methods)

something like:

select sum( d )
from (
    select a,b,c,d 
    from t1
    union all 
    select a,b,c,e
    from t2
)

Answer 3

Assuming that you are trying to create a view that gives account group and total balance with a single extra row for the total across all accounts then this view should help:

CREATE VIEW [account_balance_sums] AS
  SELECT SUM(balance) AS total, SUBSTRING (Account,0,2) AS account_group
    FROM COA
   GROUP BY account_group
  UNION ALL
  SELECT SUM(balance), 'Grand Total'
    FROM account_group

By the way, the sub-string of the first characters of the account name suggests that you have more than one piece of data in a single column. This indicates a data that is not properly normalised, which you should probably address if you want top marks. See wikipedia on normal form