March 5, 2012 at 2:09 pm
Need help with this assignment. I don’t even know where to start. Is it possible to do this in SQL? I have been doing some research and I find all sorts of different supposed ways to do this. Some say use Group By…which doesn’t work for me. Some say use string, some say Call up, etc., etc.
SQL ASSIGNMENT: 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.
This is what I have so far for my assignment and everything runs fine.
When I try to do the report it only gives me the balance for each Account. It does not allow me to group it by the first two digits. Can someone give me some guidance on this?
CREATE DATABASE KUDLERCOA
CREATE TABLE COA
(Account int Primary Key,
Description varchar(255),
ShortDescription varchar(255),
Balance money)
INSERT INTO COA
(Account, Description, ShortDescription, Balance)
VALUES
('100001', 'Petty Cash', 'Operating Cash - La Jolla', '2,034.45')
INSERT INTO COA
(Account, Description, ShortDescription, Balance)
VALUES
('100002', 'Petty Cash', 'Operating Cash - Del Mar', '2,344.88')
INSERT INTO COA
(Account, Description, ShortDescription, Balance)
VALUES
('100003', 'Petty Cash', 'Operating Cash - Encinitas', '1,894.33')
INSERT INTO COA
(Account, Description, ShortDescription, Balance)
VALUES
('100010', 'La Jolla Undeposited Receipts', 'Undeposited Receipts', '254.44')
INSERT INTO COA
(Account, Description, ShortDescription, Balance)
VALUES
('100011', 'Del Mar Undeposited Receipts', 'Undeposited Receipts', '312.55')
INSERT INTO COA
(Account, Description, ShortDescription, Balance)
VALUES
('100012', 'Encinitas Undeposited Receipts', 'Undeposited Receipts', '411.35')
INSERT INTO COA
(Account, Description, ShortDescription, Balance)
VALUES
('100020', 'Bradley Bank', 'Checking Accounting - La Jolla', '13,223.34')
INSERT INTO COA
(Account, Description, ShortDescription, Balance)
VALUES
('100021', 'Kelsey Bank', 'Checking Accounting- Del Mar', '12,878.99')
INSERT INTO COA
(Account, Description, ShortDescription, Balance)
VALUES
('100022', 'Downey Bank', 'Checking Accounting - Encinitas', '14,234.55')
INSERT INTO COA
(Account, Description, ShortDescription, Balance)
VALUES
('100030', 'La Jolla - Payroll', 'Bank Account', '7,234.44')
INSERT INTO COA
(Account, Description, ShortDescription, Balance)
VALUES
('100031', 'Del Mar - Payroll', 'Bank Account', '11,234.33')
INSERT INTO COA
(Account, Description, ShortDescription, Balance)
VALUES
('100032', 'Encinitas - Payroll', 'Bank Account', '15,223.22')
INSERT INTO COA
(Account, Description, ShortDescription, Balance)
VALUES
('100040', 'La Jolla - Workers Comp', 'Workers Compensation', '8,223.22')
INSERT INTO COA
(Account, Description, ShortDescription, Balance)
VALUES
('100041', 'Del Mar - Workers Comp', 'Workers Compensation', '10,223.22')
INSERT INTO COA
(Account, Description, ShortDescription, Balance)
VALUES
('100042', 'Encinitas - Workers Comp', 'Workers Compensation', '9,112.22')
INSERT INTO COA
(Account, Description, ShortDescription, Balance)
VALUES
('111000', 'Accounts Receivable', 'La Jolla', '7,223.77')
INSERT INTO COA
(Account, Description, ShortDescription, Balance)
VALUES
('111001', 'Reserve for Bad Debts', 'La Jolla', '3,229.99')
INSERT INTO COA
(Account, Description, ShortDescription, Balance)
VALUES
('112000', 'Accounts Receivable', 'Del Mar', '4,223.87')
INSERT INTO COA
(Account, Description, ShortDescription, Balance)
VALUES
('112001', 'Reserve for Bad Debts', 'Del Mar', '4,221.88')
INSERT INTO COA
(Account, Description, ShortDescription, Balance)
VALUES
('113000', 'Accounts Receivable', 'Encinitas', '3,765.44')
SELECT Description, ShortDescription, Balance
FROM COA
WHERE Account
BETWEEN '10000' AND '100042'
SELECT Account, Description, ShortDescription, Balance
FROM COA
WHERE Account
BETWEEN '10000' AND '100042'
SELECT Account, Description, ShortDescription, Balance
FROM COA
WHERE Account > '100042'
SELECT Account, Description, ShortDescription, Balance
FROM COA
WHERE Balance > '7,000.00'
SELECT Account, Description, ShortDescription, Balance
FROM COA
WHERE Balance < '10,000.00'
March 5, 2012 at 3:00 pm
The account column is an int, so you must convert or cast it before looking at only the first 2 numbers. Personally, if you can change this, I would not store account number as an integer. If it is not summed or used in arithmetic, I would go the VARCHAR or CHAR route. In your GROUP BY you will need to do something like this:
GROUP BY SUBSTRING(CAST(account AS VARCHAR(10)),1,2)--make VARCHAR size applicable to data
Jared
CE - Microsoft
March 5, 2012 at 3:26 pm
Greetings,
I agree that AccountId should be CHAR or VARCHAR (CHAR(8) seems right in your case).
If you need to group by the first two digits and keep AccountId as INT, you can do this (given it is always 8 chars):
SELECT CAST( Account * .0001 AS INT ), SUM( Balance )
FROM COA
GROUP BY CAST( Account * .0001 AS INT )
If you can change to CHAR, then use the string functions (this would actually work even on the INT datatype):
SELECT LEFT( Account, 2 ), SUM( Balance )
FROM COA
GROUP BY LEFT( Account, 2 )
This obviously does not give you the desired outlook. You may add a column
LEFT( Account, 2 ) AS FirstTwoDigits
to the results set like this:
SELECT LEFT( Account, 2 ) AS FirstTwoDigits, Account, Description, ShortDescription, Balance
FROM COA
and then use your reporting tool to display data groupped by the first column, FirstTwoDigits that is. Beyond that you may use the Grand Total feature of the reporting tool to display just that.
Hope it helped.
March 5, 2012 at 7:33 pm
Thanks MR@SD,
Unfortunately, that is one of the problems I was having because the software the school has us using does not have reporting software.
I can't seem to be able to use the SUM syntax and have all of the columns show as well.
For example...
SELECT LEFT( Account, 2 ) AS FirstTwoDigits, Account, Description, ShortDescription, Balance
FROM UOP
GROUP BY LEFT( Account, 2 )
Error given is
Msg 8120, Level 16, State 1, Line 1
Column 'UOP.Account' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
March 5, 2012 at 9:00 pm
Then just run two SELECT statements, insert both result sets into the same temp table (make the column match) and output it via another SELECT in the order of your choice. You could do the same thing in one statement with subqueries if cannot go with more than one statement or SP.
March 5, 2012 at 10:15 pm
MR@SD (3/5/2012)
Then just run two SELECT statements, insert both result sets into the same temp table (make the column match) and output it via another SELECT in the order of your choice. You could do the same thing in one statement with subqueries if cannot go with more than one statement or SP.
WHAT? Look, SQL returns a set of data. SQL does not return you a report unless the report is simply a set of data. If the assignment asks for a report, then you have to create a report. SQL cannot format breaks and place subtotals every X amount of lines (well, it can, but why would you?).
Your error is coming from the fact that you are grouping on LEFT(CAST(Account AS VARCHAR(8)),2) but adding other fields in the SELECT. It doesn't know how to group those fields. So, for your subtotals, you can do this:
SELECT LEFT(CAST(Account AS VARCHAR(8)),2) AS leftTwoAcct, SUM(balance) as balanceSubTotal
FROM UOP
GROUP BY LEFT(CAST(Account AS VARCHAR(8)),2)
Now your sum will work. Since this is a class, you should learn how to use the aggregate functions and GROUP BY. Everything in your SELECT has to be part of the GROUP BY or using an aggregate function. Think about it, you already grouped on the left 2 characters of the account, so the full account is meaningless.
Jared
CE - Microsoft
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply