Grouping then subtotal then grand total

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

Grouping then subtotal then grand total

Author

Topic

flatleyld
Starting Member

4 Posts

Posted - 2009-12-09 : 01:40:35

Hello everyone. I'm very new to SQL, and I'm working on a school assignment, and I need a little guidance. I have a database with only one table in it. That table has 5 fields, (AccountID, Account, Description, ShortDescription, Balance). What I need to do is display all fields with a break based on the first 2 digits of the Account field, and get a subtotal of the balance field. So each group of accounts (based on the first 2 digits) has a break and subtotal. Then at the very end I need to display the grand total of the balance field.

I can list out all of the fields and accounts with multiple select statements, and join them into one result with the UNION statement, but I can't figure out how to make that break and subtotal. Any help would be much appreciated.

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-09 : 02:20:59

Hi

May be you expect this...If not please correct the table structure , Data & your expected output...


--Table 

CREATE TABLE #TBLDATA
(
CODE VARCHAR(20), SOM INT, LEEN INT, HOWMUCH DECIMAL(15, 2), IMPORTDATE DATETIME
)

-- Data
INSERT INTO #TblDATA
SELECT 'XTL017X',  1123,1,		1432.99,	'2009-11-30 00:00:00'	UNION ALL
SELECT 'XTL017X',  112,	1,		7676.55,	'2009-12-01 00:00:00'	UNION ALL
SELECT 'XSDB68X',  101,	2754,	5432.56,	'2009-12-01 00:00:00'	UNION ALL
SELECT 'XSDB68X',  7010,3,		2754.33,	'2009-11-30 00:00:00'	UNION ALL
SELECT 'XSDX26X',  111,	1,		32433.11,	'2009-11-30 00:00:00'	UNION ALL
SELECT 'XSDX26X',  11,	3,		345676.44,	'2009-12-01 00:00:00'	UNION ALL
SELECT 'XEN019X',  62,	3,		543.22,		'2009-12-01 00:00:00'

--QUERY
SELECT	CODE,
		SUM(HOWMUCH)as Total 
FROM	#TblDATA 
GROUP BY CODE

--Drop Base Table
DROP TABLE #TBLDATA

-------------------------
R...

Sachin.Nand

2937 Posts

Posted - 2009-12-09 : 02:33:46

I guess maybe this is what he wants


CREATE TABLE #TBLDATA
(
CODE VARCHAR(20), SOM INT, LEEN INT, HOWMUCH DECIMAL(15, 2), IMPORTDATE DATETIME
)

-- Data
INSERT INTO #TblDATA
SELECT 'XTL017X',  1123,1,		1432.99,	'2009-11-30 00:00:00'	UNION ALL
SELECT 'XTL017X',  112,	1,		7676.55,	'2009-12-01 00:00:00'	UNION ALL
SELECT 'XSDB68X',  101,	2754,	5432.56,	'2009-12-01 00:00:00'	UNION ALL
SELECT 'XSDB68X',  7010,3,		2754.33,	'2009-11-30 00:00:00'	UNION ALL
SELECT 'XSDX26X',  111,	1,		32433.11,	'2009-11-30 00:00:00'	UNION ALL
SELECT 'XSDX26X',  11,	3,		345676.44,	'2009-12-01 00:00:00'	UNION ALL
SELECT 'XEN019X',  62,	3,		543.22,		'2009-12-01 00:00:00'

--QUERY



SELECT 
     CASE WHEN grptext IS NULL THEN 'SUB TOTAL' 
          WHEN Code IS NULL THEN 'GRAND TOTAL'
          ELSE Code END AS Account,
         balance
 FROM

(
SELECT CODE,
      SUM(HOWMUCH)AS balance,
      CASE WHEN GROUPING(code)=0 THEN HOWMUCH ELSE 0  END AS grptext

 FROM #TblDATA
GROUP BY 
        CODE,
        HOWMUCH WITH ROLLUP 
)T

--Drop Base Table
DROP TABLE #TBLDATA

PBUH

flatleyld
Starting Member

4 Posts

Posted - 2009-12-09 : 02:40:58

Here is some sample data from my database:

INSERT INTO Accounts (Account, Description, ShortDescription, Balance)
VALUES
(100001, 'Petty Cash ', 'Operating Cash - La Jolla', '10500'),
(100002, 'Petty Cash ', 'Operating Cash - Del Mar', '10500'),
(100003, 'Petty Cash ', 'Operating Cash - Encinitas', '10500'),
(100010, 'La Jolla Undeposited Receipts', 'Undeposited Receipts', '5360'),
(100011, 'Del Mar Undeposited Receipts', 'Undeposited Receipts', '6580'),
(100012, 'Encinitas Undeposited Receipts', 'Undeposited Receipts', '4563'),
(100020, 'Bradley Bank', 'Checking Accounting - La Jolla', '54689.65'),
(100021, 'Kelsey Bank', 'Checking Accounting- Del Mar', '69568.32'),
(100022, 'Downey Bank', 'Checking Accounting - Encinitas', '50236.66'),
(100030, 'La Jolla - Payroll', 'Bank Account', '150668.2'),
(100031, 'Del Mar - Payroll', 'Bank Account', '145623.33'),
(111000, 'Accounts Receivable', 'La Jolla', '48868'),
(111001, 'Reserve for Bad Debts', 'La Jolla', '45292'),
(112000, 'Accounts Receivable', 'Del Mar', '29076'),
(112001, 'Reserve for Bad Debts', 'Del Mar', '572'),
(113000, 'Accounts Receivable', 'Encinitas', '46484'),
(113001, 'Reserve for Bad Debts', 'Encinitas', '34797'),
(121000, 'Rustic Baguette', 'La Jolla Bakery Department Merchandise Inventory', '22310'),
(121001, 'Challah', 'La Jolla Bakery Department Merchandise Inventory', '16774'),
(121002, 'Calamata Olive Bread', 'La Jolla Bakery Department Merchandise Inventory', '47531'),
(121003, 'Ciabatta', 'La Jolla Bakery Department Merchandise Inventory', '37423'),
(121004, 'Ficelle', 'La Jolla Bakery Department Merchandise Inventory', '31801'),
(121005, 'Finnish Rye', 'La Jolla Bakery Department Merchandise Inventory', '41904'),
(121006, 'Prussian Rye', 'La Jolla Bakery Department Merchandise Inventory', '29927'),
(121007, 'Italian Semolina', 'La Jolla Bakery Department Merchandise Inventory', '38983'),
(121008, 'Sourdough Rounds and Batards', 'La Jolla Bakery Department Merchandise Inventory', '45956'),
(121009, 'Swiss Muesli', 'La Jolla Bakery Department Merchandise Inventory', '37479'),
(121010, 'Focaccia', 'La Jolla Bakery Department Merchandise Inventory', '41547'),
(121011, 'Honey Wheat', 'La Jolla Bakery Department Merchandise Inventory', '33081'),
(121012, 'Agiago Cheese', 'La Jolla Bakery Department Merchandise Inventory', '12961'),
(121013, 'French Croissants', 'La Jolla Bakery Department Merchandise Inventory', '7655'),
(121014, 'Fruit Croissants', 'La Jolla Bakery Department Merchandise Inventory', '7078'),
(131000, 'Rustic Baguette', 'Del Mar Bakery Department Merchandise Inventory', '34030'),
(131001, 'Challah', 'Del Mar Bakery Department Merchandise Inventory', '23129'),
(131002, 'Calamata Olive Bread', 'Del Mar Bakery Department Merchandise Inventory', '23409'),
(131003, 'Ciabatta', 'Del Mar Bakery Department Merchandise Inventory', '14920'),
(131004, 'Ficelle', 'Del Mar Bakery Department Merchandise Inventory', '36555'),
(131005, 'Finnish Rye', 'Del Mar Bakery Department Merchandise Inventory', '37671'),
(131006, 'Prussian Rye', 'Del Mar Bakery Department Merchandise Inventory', '8578'),
(131007, 'Italian Sssemolina', 'Del Mar Bakery Department Merchandise Inventory', '47653'),
(131008, 'Sourdough Rounds and Batards', 'Del Mar Bakery Department Merchandise Inventory', '22416'),
(131009, 'Swiss Muesli', 'Del Mar Bakery Department Merchandise Inventory', '34639'),
(131010, 'Focaccia', 'Del Mar Bakery Department Merchandise Inventory', '49256'),
(131011, 'Honey Wheat', 'Del Mar Bakery Department Merchandise Inventory', '34051'),
(131012, 'Agiago Cheese', 'Del Mar Bakery Department Merchandise Inventory', '29518')

Then I need to have it output into something like this:

Account Description Short Description Balance
100001 Petty Cash Operating Cash - La Jolla 10500
100002 Petty Cash Operating Cash - Del Mar 10500
100003 Petty Cash Operating Cash - Encinitas 10500
.
.
.
100031 Del Mar - Payroll Bank Account 145623.33
Subtotal 854635.12
111000 Accounts Receivable La Jolla 48868
111001 Reserve for Bad Debts La Jolla 45292
.
.
.
113001 Reserve for Bad Debts Encinitas 34797
Subtotal 651556.12
121000 Rustic Baguette La Jolla Bakery Department Merchandise Inventory 22310
121001 Challah La Jolla Bakery Department Merchandise Inventory 16774
.
.
.
121014 Fruit Croissants La Jolla Bakery Department Merchandise Inventory 7078
SubTotal 95296.65
131000 Rustic Baguette Del Mar Bakery Department Merchandise Inventory 34030
131001 Challah Del Mar Bakery Department Merchandise Inventory 23129
.
.
.
131012 Agiago Cheese Del Mar Bakery Department Merchandise Inventory 29518
SubTotal 651695.66
Grand Total 8559666.53

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-12-09 : 06:16:32

Crystal Reports is a GREAT place to do this.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

flatleyld
Starting Member

4 Posts

Posted - 2009-12-09 : 12:44:46

I don't have access to Crystal Reports. I need to have it done with SQL code.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources