SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Skiman1369
Starting Member

USA
5 Posts

Posted - 02/23/2011 :  16:47:56  Show Profile  Reply with Quote
I have created a table with 4 columns
CREATE TABLE [dbo].[Accounts](
[Account] [int] NOT NULL,
[Description] [varchar](60) NOT NULL,
[ShortDescription] [varchar](60) NOT NULL,
[Balance] [money] NOT NULL,
PRIMARY KEY CLUSTERED

I have also added the data which was over 400 records. The account numbers range from 100000 to 400000 I need to write 2 queries one that separates the accounts and balances into 4 account ranges (10000's 200000's ect) and totals each one account range with all the other Account numbers and balances included in the results. Then I was looking to write a query that in the result would include all the fields but at the end added a new field of total balance.

I'm stuck I can display all the data in the ranges I want and I can display the total but I can not get them together any help would be awesome.

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 02/23/2011 :  17:50:37  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
Can you post a simplified sample of data and results you want to get from there?

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page

Skiman1369
Starting Member

USA
5 Posts

Posted - 02/23/2011 :  18:35:46  Show Profile  Reply with Quote

CREATE TABLE Accounts
(
Account int NOT NULL PRIMARY KEY,
Description varchar(60) NOT NULL,
ShortDescription varchar(60) NOT NULL,
Balance money NOT NULL,
)

INSERT INTO Accounts
(Account, Description, ShortDescription, Balance)
VALUES
(100001, 'Petty Cash ', 'Operating Cash - La Jolla', 15008),
(100002, 'Petty Cash ', 'Operating Cash - Del Mar', 13781),
(100003, 'Petty Cash ', 'Operating Cash - Encinitas', 17034),
(200001, 'Boardman Fine Foods', 'La Jolla Bakery Department Vendor', 11612),
(200002, 'Boardman Fine Foods', 'Del Mar Bakery Department Vendor', 24532),
(200003, 'Boardman Fine Foods', 'Encinitas Bakery Department Vendor', 534),
(200011, 'Brooks Surf and Turf', 'La Jolla Meat and Seafood Vendor', 19801),
(300001, 'Owners Equity', 'La Jolla', 8516),
(300002, 'Owners Equity', 'Del Mar', 19370),
(300003, 'Owners Equity', 'Encinitas', 7238);

The results I would be looking for would be

Account Description (s) Balance

100001 Petty Cash Operating Cash La Jolla 15008
100002 Petty Cash Operating Cash Del Mar 13781
100003 Petty Cash Operating Cash Encinitas 17034
100000's account Total 45823 (this can be on the bottom or the the side)


The second query would need to be similar

Account Description (s) Balance

100001 Petty Cash Operating Cash La Jolla 15008
100002 Petty Cash Operating Cash Del Mar 13781
100003 Petty Cash Operating Cash Encinitas 17034
All accounts and descriptions......
Total account total 45823 (this can be on the bottom or the the side)
Go to Top of Page

Skiman1369
Starting Member

USA
5 Posts

Posted - 02/23/2011 :  21:13:14  Show Profile  Reply with Quote
Is that something thats even possible? or am I just going deal with just having labeled totals?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17635 Posts

Posted - 02/23/2011 :  22:05:11  Show Profile  Reply with Quote
add this to your query

sum(Balance) over (partition by Account / 10)



KH
Time is always against us

Go to Top of Page

Skiman1369
Starting Member

USA
5 Posts

Posted - 02/24/2011 :  10:06:35  Show Profile  Reply with Quote
That worked Thank you is there anyway to put a line break in between my totals per account?
Go to Top of Page

Skiman1369
Starting Member

USA
5 Posts

Posted - 02/24/2011 :  10:16:49  Show Profile  Reply with Quote
Select Account, Balance, sum(Balance) over (partition by Account / 10000)as Total_per_account
From Accounts

This is the code that worked for me Now I'm wondering if I can put a line break between each 10000. Any help would be great.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17635 Posts

Posted - 02/24/2011 :  10:19:51  Show Profile  Reply with Quote
quote:
Originally posted by Skiman1369

That worked Thank you is there anyway to put a line break in between my totals per account?


that is a job for your reporting tool not SQL Server


KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000