| Author |
 Topic  |
|
|
Skiman1369
Starting Member
USA
5 Posts |
 Posted - 02/23/2011 : 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
Czech Republic
518 Posts |
|
|
Skiman1369
Starting Member
USA
5 Posts |
Posted - 02/23/2011 : 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) 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) |
 |
|
|
Skiman1369
Starting Member
USA
5 Posts |
Posted - 02/23/2011 : 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)
Singapore
16746 Posts |
Posted - 02/23/2011 : 22:05:11
|
add this to your query
sum(Balance) over (partition by Account / 10)
KH
Time is always against us
|
|
|
|
Skiman1369
Starting Member
USA
5 Posts |
Posted - 02/24/2011 : 10:06:35
|
| That worked Thank you is there anyway to put a line break in between my totals per account? |
|
|
|
Skiman1369
Starting Member
USA
5 Posts |
Posted - 02/24/2011 : 10:16:49
|
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. |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 02/24/2011 : 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
Time is always against us
|
|
|
| |
Topic |
|
|
|
Query Help
