0

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]
Improve this question

4 Answers 4

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks... that is for grouping by the first 2 numbers of the account. How do I pick the first 2 numbers?
You will need a function, you can specify functions in the GROUP BY clause, have a think about it, give it a try and come back here if you get stuck. It will be different depending on the way the account number is stored, is it a VARCHAR or a INT.
Yes, you can specify non-aggregate functions in the GROUP BY clause. GROUP BY LEFT(account, 2) works for me on SQL Server 2005...
1

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.

Improve this answer

2 Comments

Thanks looking into that cube rollup Here is what I have so far... 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]
Does that work? you need to put your SUBSTRING function in the GROUP BY i'd expect.
1

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), ...
Improve this answer

5 Comments

is there a benefit to use SUBSTRING instead of LEFT?
No, LEFT makes sense in this context. For a student, if you're only going to learn one command, SUBSTRING is good to understand as it is more general.
You need to correct the GROUP BY - it won't return an error, but the sum won't reflect the LEFT/SUBSTRING for grouping - you'll get a row for each group.
He's actually grouping by the alias (which isn't allowed in SQL Server)
@Tom yes that is correct, it's worth noting that you can ORDER BY an alias.
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.