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 |
|
duhaas
Constraint Violating Yak Guru
310 Posts |
 Posted - 2006-08-23 : 14:47:42
|
| Is it possible using the following query as a base, to get details for the grouped # of accounts, example:CURRENT SQLselect count(*) as [# of accounts], categorydescription, sum([account market value]) as [Account Market Value] from snapsrawwhere Relaionship is not null and BranchState = 'Arizona'group by categorydescriptionorder by [account market value] descCURRENT OUTPUT# category account value12 Defined Benefit - Qualified 137279560.280086 IRA - Investment 35756732.2100how would i get it to show me what makes up the 12 total and 86 totalaccounts??? sort of like a details for the grouping, i have sql reporting services, just not figuring out how to do it. any help would be greatly apprecaited |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-23 : 14:54:51
|
| To get the details of the grouped rows, you can use a derived table. Check out my solution in this post from today:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70940The key is that the derived table contains your grouping query, then you join on it with all of the columns involved in the GROUP BY plus the aggregate function(s).Tara Kizer |
 |
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2006-08-29 : 15:45:47
|
| Can i please than confirm that I should get the results as follows:name Total #of accounts total market valueDefined Benefits 2 2,000,000,000 account 1 1,000,000,000 account 2 1,000,000,000or do i need some reporting tool to make it look like that? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-29 : 15:47:35
|
| I don't know as you haven't provided enough information for us to determine that. I doubt a reporting tool is needed to get your data to display that way.Tara Kizer |
|
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2006-08-29 : 15:59:32
|
| Current Query:select distinct categorydescription as [Service Type],count (*) as [Total # of Accounts], sum([Account Market Value]) as [Total Market value] from snapsrawgroup by categorydescriptionorder by [Total Market Value] descSample OutputCATEGORYDESCRIPTION|#of accounts|SUM of thos accounts ----HEADER|Custodial Agency|10|33332250895.5000 ..... .....|Qualified Defined Contribution|7|6994827707.6400 ..... .....Now I also want to display underneath each main headind the top 5 accounts in each of those categorys and their market value, so instead of lets say the ..... show me the top account in that category with its market value and so on? does that help? apologizes for lack of explanation. as always your help is greatly appreciated |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-08-29 : 16:06:55
|
Yes, you should use a reporting tool to show group headers/footers and totals. The SQL side of things is for returning the raw data, summarized and filtered and ready to go. Then, from there, your reporting tool takes that data and applies grouping, indents, totals, and so on. Ultimately, to get these results to display *somewhere* other than in the query analyzer, you'll need a tool like reporting services to create your nice output on paper or HTML or wherever you will eventually display it. Do your formatting there.EDIT: It helps to put [ code ] [ / code ] tags around your post where you want things to be formatted with spacing and all that. Here is your post with code tags around it:name Total #of accounts total market valueDefined Benefits 2 2,000,000,000 account 1 1,000,000,000 account 2 1,000,000,000 - Jeff |
|
|
|
|
|
|
|
|
Grouping and Detail