Create View SQL

Question

I am a student this is homework. I use SQL server to check my work but have to write script by hand.

Create and display a VIEW that displays the account balance subtotals for each account group. Account group is defined as the first two digits of the account number. Display a grand total of the balance column at the end of the display.

I have one table with 4 columns. Account(numbers), Description, Short_Description, and Balance(money). The Account numbers range from 100001-610003. COA is the chart of accounts that is an excel link. Thanks for any advice.

This is what I have so far...

CREATE VIEW [account_balance_sums]
 AS 
 SELECT Account, Short_Description,Balance
 FROM COA
 Where Account, (first two digits 10-61 of account #)
 AND sum
 GO

SELECT * FROM [account_balance_sums]

Answer 1

I don't want to do it for you as you will never learn but you will need the GROUP BY clause.

Answer 2

Given that this is homework I just want to give you enough to get going, but it seems to me that you want to do something like

SELECT ...., SUM(Balance) as group_subtotal, SUBSTRING(Account,0,2) AS account_group
FROM ....
GROUP BY SUBSTRING(Account,0,2), ...

Answer 3

Since this is homework, I don't want to give it away, but you'll need to add up the balances, so you'll need to use the SUM function.

Also, since you want to SUM them by the first two digits of the account number, you'll need to use GROUP BY to create the groups, and SUBSTR to get the first two digits.

Exactly how you put it all together is the real trick of course.

Answer 4

So, GROUP BY and SUM will get you most of the way. Then use WITH CUBE/ROLLUP to get the grand total. You may want to look into the GROUPING() function if you go this route.