creating a SQL report with subtotal,grandtotal

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

creating a SQL report with subtotal,grandtotal

Author

Topic

michaelu30
Starting Member

2 Posts

Posted - 2009-03-31 : 13:34:02

DROP TABLE KudlerFoods_Accounts;

CREATE TABLE KudlerFoods_Accounts (
Account VARCHAR(60) not null Primary KEY,
Description VARCHAR(100) not null,
Short_Description VARCHAR(200) not null,
Balance MONEY null );

INSERT INTO KudlerFoods_Accounts (Account,Description,Short_Description,Balance)
SELECT 100001,'Petty Cash','Operating Cash-La Jolla',NULL
UNION ALL
SELECT 100002,'Petty Cash','Operating Cash-Del Mar',NULL
UNION ALL
SELECT 100003,'Petty Cash','Operating Cash-Encinitas',NULL
UNION ALL
SELECT 100010,'La Jolla Undeposited Receipts','Undeposited Receipts',NULL
UNION ALL
SELECT 100011,'Del Mar Undeposited Receipts','Undeposited Receipts',NULL
UNION ALL
SELECT 100012,'Encinitas Undeposited Receipts','Undeposited Receipts',NULL
UNION ALL
SELECT 100020,'Bradley Bank','Checking Accounting-La Jolla',NULL
UNION ALL
SELECT 100021,'Kelsey Bank','Checking Accounting-Del Mar',NULL
UNION ALL
SELECT 100022,'Downey Bank','Checking Accounting-Encinitas',NULL
UNION ALL
SELECT 100030,'La Jolla-Payroll','Bank Account',NULL
UNION ALL
SELECT 100031,'Del Mar-Payroll','Bank Account',NULL
UNION ALL
SELECT 100032,'Encinitas-Payroll','Bank Account',NULL
UNION ALL
SELECT 111000,'Accounts Receivable','La Jolla',NULL
UNION ALL
SELECT 111001,'Reserve for Bad Debts','La Jolla',NULL
UNION ALL
SELECT 112000,'Accounts Receivable','Del Mar',NULL
UNION ALL
SELECT 112001,'Reserve for Bad Debts','Del Mar',NULL
UNION ALL
SELECT 113000,'Accounts Receivable','Encinitas',NULL
UNION ALL
SELECT 113001,'Reserve fpr Bad Debts','Encinitas',NULL
UNION ALL
SELECT 121000,'Rustic Baguette','La Jolla Bakery Department Merchandise Inventory',NULL
UNION ALL
SELECT 121001,'Challah','La Jolla Bakery Department Merchandise Inventory',NULL
UNION ALL
SELECT 121002,'Calamata Olive Bread','La Jolla Bakery Department Merchandise Inventory',NULL
UNION ALL
SELECT 121003,'Ciabatta','La Jolla Bakery Department Merchandise Inventory',NULL
UNION ALL
SELECT 121004,'Ficelle','La Jolla Bakery Department Merchandise Inventory',NULL
UNION ALL
SELECT 131000,'Angus Beef','La Jolla Meat & Seafood Merchandise Inventory',NULL
UNION ALL
SELECT 131001,'Wild Turkey','La Jolla Meat & Seafood Merchandise Inventory',NULL
UNION ALL
SELECT 131002,'Duck','La Jolla Meat & Seafood Merchandise Inventory',NULL
UNION ALL
SELECT 131003,'Pheasant','La Jolla Meat & Seafood Merchandise Inventory',NULL
UNION ALL
SELECT 131004,'Quail','La Jolla Meat & Seafood Merchandise Inventory',NULL
UNION ALL
SELECT 141000,'Rustic Baguette','Encinitas Bakery Department Merchandise Inventory',NULL
UNION ALL
SELECT 141001,'Challah','Encinitas Bakery Department Merchandise Inventory',NULL
UNION ALL
SELECT 141002,'Calamata Olive Bread','Encinitas Bakery Department Merchandise Inventory',NULL
UNION ALL
SELECT 141003,'Ciabatta','Encinitas Bakery Department Merchandise Inventory',NULL
UNION ALL
SELECT 141004,'Ficelle','Encinitas Bakery Department Merchandise Inventory',NULL;

Update KudlerFoods_Accounts
SET Balance= 15000
WHERE Account=100001;
Update KudlerFoods_Accounts
SET Balance= 24000
WHERE Account=100002;
Update KudlerFoods_Accounts
SET Balance= 60000
WHERE Account=100003;
Update KudlerFoods_Accounts
SET Balance= 36000
WHERE Account=100010;
Update KudlerFoods_Accounts
SET Balance= 27000
WHERE Account=100011;
Update KudlerFoods_Accounts
SET Balance= 44000
WHERE Account=100012;
Update KudlerFoods_Accounts
SET Balance= 120000
WHERE Account=100020;
Update KudlerFoods_Accounts
SET Balance= 175000
WHERE Account=100021;
Update KudlerFoods_Accounts
SET Balance= 225000
WHERE Account=100022;
Update KudlerFoods_Accounts
SET Balance= 300000
WHERE Account=100030;
Update KudlerFoods_Accounts
SET Balance= 400000
WHERE Account=100031;
Update KudlerFoods_Accounts
SET Balance= 425000
WHERE Account=100032;
Update KudlerFoods_Accounts
SET Balance= 95000
WHERE Account=111000;
Update KudlerFoods_Accounts
SET Balance= 30000
WHERE Account=111001;
Update KudlerFoods_Accounts
SET Balance= 72000
WHERE Account=112000;
Update KudlerFoods_Accounts
SET Balance= 40000
WHERE Account=112001;
Update KudlerFoods_Accounts
SET Balance= 136000
WHERE Account=113000;
Update KudlerFoods_Accounts
SET Balance= 50000
WHERE Account=113001;
Update KudlerFoods_Accounts
SET Balance= 5000
WHERE Account=121000;
Update KudlerFoods_Accounts
SET Balance= 2000
WHERE Account=121001;
Update KudlerFoods_Accounts
SET Balance= 1300
WHERE Account=121002;
Update KudlerFoods_Accounts
SET Balance= 1000
WHERE Account=121003;
Update KudlerFoods_Accounts
SET Balance= 1600
WHERE Account=121004;
Update KudlerFoods_Accounts
SET Balance= 1400
WHERE Account=131000;
Update KudlerFoods_Accounts
SET Balance= 2700
WHERE Account=131001;
Update KudlerFoods_Accounts
SET Balance= 2300
WHERE Account=131002;
Update KudlerFoods_Accounts
SET Balance= 1700
WHERE Account=131003;
Update KudlerFoods_Accounts
SET Balance= 2400
WHERE Account=131004;
Update KudlerFoods_Accounts
SET Balance= 3000
WHERE Account=141000;
Update KudlerFoods_Accounts
SET Balance= 1900
WHERE Account=141001;
Update KudlerFoods_Accounts
SET Balance= 2900
WHERE Account=141002;
Update KudlerFoods_Accounts
SET Balance= 3100
WHERE Account=141003;
Update KudlerFoods_Accounts
SET Balance=1200
WHERE Account=141004;

I need my format to be

Account Description Short Description Balance
10
10
10
10
10
10
10
Subtotal
11
11
11
11
Subtotal

Grand Total at the end

My two attempts are as follows:

Select Account, Description, Short_Description,Balance
FROM KudlerFoods_Accounts
WHERE Account LIKE '10%'
COMPUTE SUM(Balance);
Select Account, Description, Short_Description,Balance
FROM KudlerFoods_Accounts
WHERE Account LIKE '11%'
COMPUTE SUM(Balance);
Select Account, Description, Short_Description,Balance
FROM KudlerFoods_Accounts
WHERE Account LIKE '12%'
COMPUTE SUM(Balance);
Select Account, Description, Short_Description,Balance
FROM KudlerFoods_Accounts
WHERE Account LIKE '13%'
COMPUTE SUM(Balance);
Select Account, Description, Short_Description,Balance
FROM KudlerFoods_Accounts
WHERE Account LIKE '14%'
COMPUTE SUM(Balance);

SELECT Account, Description,Short_Description,SUM(Balance)
FROM KudlerFoods_Accounts
GROUP BY Account,Description,Short_Description,Balance with Rollup
ORDER BY 1;

I'm not getting anywhere close here....please help!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-31 : 13:38:08

bring detail data to report and apply three groupings in report on Account, Description,Short_Description

michaelu30
Starting Member

2 Posts

Posted - 2009-03-31 : 13:49:45

I'm sorry...I'm still wet behind the ears with regards to SQL.....do you mean to use a nested query? I'm not sure what you mean by bring detail data to report Thanks in advance!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-01 : 12:48:10

dont use group by in query. just bring data as it is from table. then in report apply three groupings to show various level of data

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources