This is the assignment:
• Create SQL database Chart of Accounts report. The report will display all fields with a break based on the first two digits of the account number and subtotal of the balance field at each break. A grand total of the balance field will be provided at the end of the report. Submit query, screen shots of output(MS Word) and Chart of Accounts report for grading.
Below is the query statement I have been trying to use, but I can't seem to figure out the syntax to get it to group the account numbers. The report is giving me the balance of each account number instead of the group of account numbers. For example, I have several account numbers beginning with 12 and several with 13, 14, etc. How do I group all of the 12's and get their balance, and the 13's, etc. Any assistance would be much appreciated. Thanks.
SELECT Accounts.AccountID, SUM(Inventory.QuantityAvailable * Items.ItemPrice) AS 'Balance' FROM Store_Locations, Inventory, Items, Accounts, Departments WHERE Store_Locations.LocationID=Accounts.Location2ID AND Inventory.ItemName2 = Items.ItemName AND Accounts.Location2ID = Store_Locations.LocationID AND Accounts.Department2ID = Departments.DepartmentID AND Accounts.Inventory2ID = Inventory.InventoryID GROUP BY AccountID WITH CUBE;