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.
| Author |
Topic |
|
Skiman1369
Starting Member
5 Posts |
 Posted - 2011-02-23 : 16:47:56
|
| 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
518 Posts |
Posted - 2011-02-23 : 17:50:37
|
| Can you post a simplified sample of data and results you want to get from there?MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
 |
|
|
Skiman1369
Starting Member
5 Posts |
Posted - 2011-02-23 : 18:35:46
|
| 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) Balance100001 Petty Cash Operating Cash La Jolla 15008100002 Petty Cash Operating Cash Del Mar 13781100003 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) Balance100001 Petty Cash Operating Cash La Jolla 15008100002 Petty Cash Operating Cash Del Mar 13781100003 Petty Cash Operating Cash Encinitas 17034All accounts and descriptions...... Total account total 45823 (this can be on the bottom or the the side) |
|
|
|
Skiman1369
Starting Member
5 Posts |
Posted - 2011-02-23 : 21:13:14
|
| Is that something thats even possible? or am I just going deal with just having labeled totals? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-02-23 : 22:05:11
|
add this to your querysum(Balance) over (partition by Account / 10)
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
Skiman1369
Starting Member
5 Posts |
Posted - 2011-02-24 : 10:06:35
|
| That worked Thank you is there anyway to put a line break in between my totals per account? |
|
|
|
Skiman1369
Starting Member
5 Posts |
Posted - 2011-02-24 : 10:16:49
|
| Select Account, Balance, sum(Balance) over (partition by Account / 10000)as Total_per_accountFrom AccountsThis 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. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-02-24 : 10:19:51
|
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[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|
|
Query Help