InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 10:39:56 GMT20http://www.sqlservercentral.com/Forums/Topic822159-8-1.aspxJB, I would recommend starting your own thread to ask for help, instead of posting in a 2 year old thread.Thu, 18 Aug 2011 10:49:44 GMTclubbavichhttp://www.sqlservercentral.com/Forums/Topic822159-8-1.aspxI am trying to a non aggregate rollup... sort of.I have columns for student, term1, term2, term3, etc.The data under term is actually a course that is being taken that term so there can be multiple rows for a student. I create the query dynamically based on a date parameter. All of this works fine except I am getting duplicated rows that I can't get rid of using DISTINCT. I am POSITIVE every one of the columns is identical to another row. I have tried using isnull around every data column to make sure i have a blank string in the column (in the back of my head I always have the little voice that says "a NULL may not be equal to another NULL").Now the REALLY UGLY part. Typically I'm going beyond 32 columns so SQL throws up with the can't have more than 32 groups error. I can do this manually in Excel but thats manual. I don't like doing things twice or even having to use Excel to manipulate data.Any ideas? I can't really post the code as the company I work for is really paranoid about things getting posted publicly.TIA,jbWed, 17 Aug 2011 13:16:26 GMTJBANKOhttp://www.sqlservercentral.com/Forums/Topic822159-8-1.aspxThink of not using any of the first three columns, and instead constructing the column you want; use the grouping function to build that new column and also to contrive the sequence you want:Something like:select case when grouping(companyname)=1 then 'Co: ' + companyName when grouping(districtname)=1 then 'Dist: ' + districtname else 'Store: ' + StoreNbr end as 'Column 1'-- , companyname, districtname, storenbr , sum( Mature) Mature , sum( Math) Math , sum(AudioBooks) Audiofrom bookstores bs inner join booksales sls on sls.storeid = bs.storeId-- where sls.BusinessDate ... (I presume)group by companyname, districtname, storeNbrwith rolluporder by grouping(companyname) desc , grouping(districtname) desc , storeNbr ascMon, 30 May 2011 21:47:39 GMTThomas Considinehttp://www.sqlservercentral.com/Forums/Topic822159-8-1.aspxWOW!!! This is my exactly my point. Another post where there was no answer but a lecture. [quote]Don't go back and edit your previous posts since we cannot keep on tracking if you have edited the original post and look for changes or new update[/quote]Sorry but I feel it is better for someone linking to this thread from Google that they not have to go through lots of clarifications. It will turn them off before getting to potentially valuable knowledge.I was referring to topics such as Ranse, Dense_Ranks, Patitioning, WindowsSun, 22 Nov 2009 18:10:59 GMTTheHTMLDJhttp://www.sqlservercentral.com/Forums/Topic822159-8-1.aspxHi,Don't go back and edit your previous posts since we cannot keep on tracking if you have edited the original post and look for changes or new update. I was surprised by seeing the initial post and it have been changing very day I have seen it in the last 2 days.Now I don't even remember what you original post looks like.... don't repeat that next time..Sun, 22 Nov 2009 16:42:03 GMTBru Medishettyhttp://www.sqlservercentral.com/Forums/Topic822159-8-1.aspx[quote][b]TheHTMLDJ (11/22/2009)[/b][hr]I checked out your website. Do you have any plans for video tutorials about complex topics such as Analytics? I have not been able to find these anywhere and I think would be useful.I checkout out Bru's website.[/quote]topics such as Analytics? or Analysis Server (SSAS). I did not understand..Yes well I am planning to come up with some videos and currently taking suggestions on what topics that are requested... You can make post in the Feedback forum about the topics that I would consider..Sun, 22 Nov 2009 16:36:52 GMTBru Medishettyhttp://www.sqlservercentral.com/Forums/Topic822159-8-1.aspxA spreadsheet is attached of the data with companyname and districtname column. It does appear there is duplicate data for some stores on some dates. Assume this is by design. The relevant part is the summations and the rolloup. I will however repost data when I get a chance.Sun, 22 Nov 2009 14:05:26 GMTTheHTMLDJhttp://www.sqlservercentral.com/Forums/Topic822159-8-1.aspxI checked out your website. Do you have any plans for video tutorials about complex topics such as Analytics? I have not been able to find these anywhere and I think would be useful.I checkout out Bru's website.Sun, 22 Nov 2009 13:37:22 GMTTheHTMLDJhttp://www.sqlservercentral.com/Forums/Topic822159-8-1.aspx[quote][b]lmu92 (11/22/2009)[/b][hr]It looks like either your result set does not match your requirement or your requirement is still unclear...Example:[quote]companyname districtname storenbr businessdate Mature Math AudioBooksOnlineDotCom Huntsville 0005 2009-09-07 00:00:00.000 1752 1752 360OnlineDotCom Huntsville 0005 2009-09-07 00:00:00.000 2388 2388 519OnlineDotCom Huntsville 0005 2009-09-07 00:00:00.000 1993 1993 379... It shows that there is more than one row for the same day and the same store.[/quote][/quote]Oversight on my part in preparing test data but should not matter. Will just have double the amounts for that day. [quote]You also didn't state on how the business date column needs to be sorted....please have a look at BOL [/quote]I tried this and got interesting date error: [code]SELECT stores.companyname as companyname , '' as districtname , '' as storenbr , CONVERT(VARCHAR(9), businessdate, 6) as businessdate ,SUM(Mature) as Mature ,Sum(Math) as Math ,Sum(AudioBooks) as AudioBooks from booksales sales inner join bookstores stores on stores.storeid = sales.storeid group by companyname, CONVERT(VARCHAR(9), businessdate, 6)order by companyname, districtname, storenbr[/code]I got an error stating that at least one column in GROUP BY could not be be in outer query. This is not the exact message and I can't reproduce. but when I changed to -- GROUP BY companyname, businessdate -- in the result set, the dates were all the same. I actually expected to get error because you typically can't use a contrived alias in a GROP by clause. This is a NULL point now because it seems to work. No pun intended. [quote]You also haven't answer my question regarding the reason to order it by storenbr.[/quote]Because that is the way the user wants to see it. I don't know any other way of answering. [ quote] Maybe you should ask your question on a Oracle forum![/quote] This has to be done in both SQL Server and Oracle. [quote]I'm outta here. Have fun, whoever stays in here...[/quote]In my comparison against a DBA and programmer, I think I may have insulted you so I have removed. I do value your help and any help you can provide but I would appreciate your not discouraging others.To clarify, here is what I need at this point: Condense the UNION ALL statement into a statement with a ROLLUP and eliminate the companyname and districtname columns. Forget all else.Sun, 22 Nov 2009 13:29:30 GMTTheHTMLDJhttp://www.sqlservercentral.com/Forums/Topic822159-8-1.aspxIt looks like either your result set does not match your requirement or your requirement is still unclear...Example:[quote]companyname districtname storenbr businessdate Mature Math AudioBooksOnlineDotCom Huntsville 0005 2009-09-07 00:00:00.000 1752 1752 360OnlineDotCom Huntsville 0005 2009-09-07 00:00:00.000 2388 2388 519OnlineDotCom Huntsville 0005 2009-09-07 00:00:00.000 1993 1993 379[/quote]It shows that there is more than one row for the same day and the same store.That's different than your requirement from a few posts back.You also didn't state on how the business date column needs to be sorted.You also haven't answer my question regarding the reason to order it by storenbr.Regarding your requirement to get the date format correct: I think that's the easy part you can help us help you: please have a look at BOL (SQL OnlineHelp system installed together with your SQL Server).Two more notes:a) [quote]But if i can get data in correct sort order, I can avoid doing a dataview in my .NET program. [b]Not important.[/b] [/quote]That really means something to me (someone who's trying to help you!)! Sounds like "It's not important at all. Just want to see if you folks in the forum can solve it." b)[quote]I will ultimately have to convert this over to an Oracle procedure which has different syntax but T-SQL will work for now. [/quote]Maybe you should ask your question on a Oracle forum!Final statement from my side:Since it is a) unclear, what the OP want,b) obviously, not important to the OP andc) for Oracle instead of SQL ServerI'm outta here. Have fun, whoever stays in here...Sun, 22 Nov 2009 11:29:26 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic822159-8-1.aspxThis following query gives me exactly what I need except the companyname and districtname columns need to be eliminated1) when the districtname is empty, the companyname becomes the districtname2) when the storenbr is empty the districtname becomes the storenbr The districts and companies are all unique and storenbrs are all unique , the client applicaiton will know that a particular record represents a store, a district,or a company [code]SELECT companyname , districtname , storenbr , businessdate , Mature , Math , AudioBooks from booksales sales inner join bookstores stores on stores.storeid = sales.storeid union allSELECT stores.companyname as companyname , stores.districtname as districtname , '' as storenbr , businessDate ,SUM(Mature) as Mature ,Sum(Math) as Math ,Sum(AudioBooks) as AudioBooks from booksales sales inner join bookstores stores on stores.storeid = sales.storeid group by companyname, districtname, businessdate union all SELECT stores.companyname as companyname , '' as districtname , '' as storenbr , businessdate ,SUM(Mature) as Mature ,Sum(Math) as Math ,Sum(AudioBooks) as AudioBooks from booksales sales inner join bookstores stores on stores.storeid = sales.storeid group by companyname, businessdateorder by companyname, districtname, storenbr, businessdate[/code]Of course, this can be simplified with rollup. Note: -- all columns in my original query were not necessary -- the businessdate needs to be in dd-Mon-yy format Sun, 22 Nov 2009 02:29:46 GMTTheHTMLDJhttp://www.sqlservercentral.com/Forums/Topic822159-8-1.aspxIf I understand correctly you want to have the rollup result value before the values rolled up as opposed to the SQL standard.The closest I could come up with is the following:[code="sql"]SELECT isnull(CAST(storenbr AS VARCHAR(50)),isnull(stores.districtname,stores.companyname)) AS Grp ,SUM(Mature) as Mature ,Sum(Cooking) as Cooking ,Sum(Exercise) as Exercise ,Sum(Math) as Math ,Sum(AudioBooks) as AudioBooks ,Sum(Childrens) as Childrens ,Sum(Spanish) as Spanish from booksales sales inner join bookstores stores on stores.storeid = sales.storeid INNER JOIN ( SELECT [COMPANYNAME],[DISTRICTNAME],MIN([STORENBR]) AS MIN_STORENBR FROM bookstores GROUP BY [COMPANYNAME],[DISTRICTNAME] ) stores2 ON stores2.[COMPANYNAME] = stores.[COMPANYNAME] AND stores2.[DISTRICTNAME] = stores.[DISTRICTNAME]group BY stores.companyname , stores.districtname , MIN_STORENBR , STORES.STORENBR WITH rollup HAVING ISNULL(MIN_STORENBR,stores.storenbr) IS NOT NULL OR stores.companyname IS NOT NULL AND stores.districtname IS NULL ORDER BY isnull(MIN_STORENBR,' ' + stores.companyname), isnull(storenbr,' ' + isnull(stores.districtname,' '+ stores.companyname))[/code]The result set will display the rollup values first and the district order as requested.However, your requirement to sort by district with the smallest shop number first is really strange (and it requires an additional join just for display order)...Another question: What is the specific reason to do this with SQL anyway? Usually, rollup and sorting (especially nonstandard) can and should be done by the application...Edit: wrong SQL posted before ...Sat, 21 Nov 2009 13:32:33 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic822159-8-1.aspxHi,I understand that is not simple, since you must be working in different time zone, I ma in EST in USA, so I could not reply in between, while you guys are trying to figure it out.My question is ,what is the output you want to look like? Can you put that in a tabular format in excel and attach here, so that makes easier to figure out what you need. I could only see in your first post how you wanted the out put but what was this[code="plain"]Records must be in this specific order: OnlineDotCom (Company totals)Memphis (District totals)0001 (storeNbr totals)0002 (storeNbr totals)Tupelo (districtName totals)0003 (storeNbr totals)0004 (storeNbr totals)[/code]Almost all the replies in between were how you want,what are the columns in order by, etc etc.. What columns constitutes Totals? Which columns should be summed up to get that total figure? A better way of displaying would be in a tabular format in excel, that would be helpful even any one trying to figure out what you want achieve.Sat, 21 Nov 2009 11:42:39 GMTBru Medishettyhttp://www.sqlservercentral.com/Forums/Topic822159-8-1.aspx[quote][b]arun.sas (11/21/2009)[/b][hr][quote][b]TheHTMLDJ (11/20/2009)[/b]Im not sure what you meant by what formats need to be retrieved.[/quote]I mean[code="plain"]Records must be in this specific order: OnlineDotCom (Company totals)Memphis (District totals)0001 (storeNbr totals)0002 (storeNbr totals)Tupelo (districtName totals)0003 (storeNbr totals)0004 (storeNbr totals)[/code]is now retrieved correctly?[/quote]Yes, records must be in exactly that order where the grand total is the first record, then the Memphis district totals, then the 2 stores within that district, then the Tupelo district and its 2 stores. There are more stores and more districts but i have only provided a subset of data.I'm a little tired but the reason I had the sort the first way is because the numbers in the left columns above are the store nbrs. meaning that when the store number is blank due to the rollup, then the district name should be put in the storenbr. So you end up with the sort just being on storenbr and businessdate.Sat, 21 Nov 2009 00:36:59 GMTTheHTMLDJhttp://www.sqlservercentral.com/Forums/Topic822159-8-1.aspx[quote][b]TheHTMLDJ (11/20/2009)[/b]Im not sure what you meant by what formats need to be retrieved.[/quote]I mean[code="plain"]Records must be in this specific order: OnlineDotCom (Company totals)Memphis (District totals)0001 (storeNbr totals)0002 (storeNbr totals)Tupelo (districtName totals)0003 (storeNbr totals)0004 (storeNbr totals)[/code]is now retrieved correctly?Sat, 21 Nov 2009 00:05:44 GMTarun.sashttp://www.sqlservercentral.com/Forums/Topic822159-8-1.aspx[quote]Ok, from the sample data I assumed that the company is static, so that I removed the company from order by class, how ever when the change of the order class, your needed is fulfilled? Or what formats do need to retrieved?[/quote]yes company is static. I put it in the ORDER BY for future maintenance. Im not sure what you meant by what formats need to be retrieved.Fri, 20 Nov 2009 23:57:18 GMTTheHTMLDJhttp://www.sqlservercentral.com/Forums/Topic822159-8-1.aspx[quote][b]TheHTMLDJ (11/20/2009)[/b][hr][quote][b]arun.sas (11/20/2009)[/b][hr][quote][b]TheHTMLDJ (11/20/2009)[/b][hr][quote]Post the schema of the booksales table[/quote]Do you mean my first table definition?[/quote]Yes, but not now, because you edit the first post, and what formats do need to retrieved?From the original post, you need the format by doing just like[code="sql"]Order By DISTRICTNAME,storenbr ,businessdate [/code][/quote]Sorry, we must have crossed paths.Make the order companyname, districtname, storenbr, businessdate[/quote][b]Ok[/b], from the sample data I assumed that the company is static, so that I removed the company from order by class, how ever when the change of the order class, your needed is fulfilled? Or what formats do need to retrieved?Fri, 20 Nov 2009 23:46:06 GMTarun.sashttp://www.sqlservercentral.com/Forums/Topic822159-8-1.aspx[quote][b]arun.sas (11/20/2009)[/b][hr][quote][b]TheHTMLDJ (11/20/2009)[/b][hr][quote]Post the schema of the booksales table[/quote]Do you mean my first table definition?[/quote]Yes, but not now, because you edit the first post, and what formats do need to retrieved?From the original post, you need the format by doing just like[code="sql"]Order By DISTRICTNAME,storenbr ,businessdate [/code][/quote]Sorry, we must have crossed paths.Make the order companyname, districtname, storenbr, businessdateFri, 20 Nov 2009 23:38:19 GMTTheHTMLDJhttp://www.sqlservercentral.com/Forums/Topic822159-8-1.aspx[quote][b]TheHTMLDJ (11/20/2009)[/b][hr][quote]Post the schema of the booksales table[/quote]Do you mean my first table definition?[/quote]Yes, but not now, because you edit the first post, and what formats do need to retrieved?From the original post, you need the format by doing just like[code="sql"]Order By DISTRICTNAME,storenbr ,businessdate [/code]Fri, 20 Nov 2009 23:26:51 GMTarun.sashttp://www.sqlservercentral.com/Forums/Topic822159-8-1.aspx[quote]Post the schema of the booksales table[/quote]Do you mean my first table definition?Fri, 20 Nov 2009 23:07:57 GMTTheHTMLDJhttp://www.sqlservercentral.com/Forums/Topic822159-8-1.aspxthe select statement should be: [code]SELECT [COMPANYNAME] ,[DISTRICTNAME] ,[STORENBR] ,[BusinessDate] ,SUM(Mature) as Mature ,Sum(Cooking) as Cooking ,Sum(Exercise) as Exercise ,Sum(Math) as Math ,Sum(AudioBooks) as AudioBooks ,Sum(Childrens) as Childrens ,Sum(Spanish) as Spanish ,[STORENAME]from booksales sales inner join bookstores stores on stores.storeid = sales.storeidgroup by businessdate , stores.companyname , stores.districtname , STORES.STORENBR , STORES.STORENAME Order By businessdate, storenbr[/code]Fri, 20 Nov 2009 23:04:40 GMTTheHTMLDJhttp://www.sqlservercentral.com/Forums/Topic822159-8-1.aspxUsed the code given by you. Still not complete code.. at least as of now....I get the following error [code="plain"]Msg 207, Level 16, State 1, Line 29Invalid column name 'week_date'.Msg 207, Level 16, State 1, Line 34Invalid column name 'weeknbr'.[/code]Fri, 20 Nov 2009 21:14:55 GMTBru Medishettyhttp://www.sqlservercentral.com/Forums/Topic822159-8-1.aspxHi,Post the schema of the [b]booksales [/b]table, its helpful us to understand better.Fri, 20 Nov 2009 20:27:27 GMTarun.sashttp://www.sqlservercentral.com/Forums/Topic822159-8-1.aspxThis is an edited post: My wife kicked me out of bed Friday night because I insulted her friend at a dinner party. Her friend does in fact havea double-chin, but I should not have stated that in front of our minister. So I was very tired when I wrote the original post and provided data with duplicates. I perhaps insulted a DBA on this forum when expressing an unsolicited opinion. The UNION ALL statement below gives me exactly what I needIt needs to be condensed using ROLLUP to both district and company level Also I want to eliminate the companyname and districtname columns based on the following: When storenbr is null, districtname should show in Storenbr, When districtname is null the companyname should show up in districtname[code]IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Booksales]') AND type in (N'U'))DROP TABLE [dbo].[Booksales]GO CREATE TABLE [dbo].[Booksales]( [Storeid] [decimal](18, 0) NULL, [BusinessDate] [datetime] NULL, [Mature] [decimal](18, 0) NULL, [Math] [decimal](18, 0) NULL, [AudioBooks] [decimal](18, 0) NULL) ON [PRIMARY] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BOOKSTORES]') AND type in (N'U'))DROP TABLE [dbo].[BOOKSTORES]CREATE TABLE [dbo].[BOOKSTORES]( [COMPANYNAME] [varchar](40) NULL, [DISTRICTNAME] [varchar](50) NULL, [STOREID] [char](4) NULL, [STORENAME] [varchar](70) NULL, [STORENBR] [char](4) NOT NULL) ON [PRIMARY]INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(20,'Aug 25 2008 12:00:00:000AM',10606,11806,6370)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(20,'Aug 24 2009 12:00:00:000AM',7371,8271,4425)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(20,'Aug 31 2009 12:00:00:000AM',9374,10574,5839)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(20,'Sep 7 2009 12:00:00:000AM',3265,3452,1668)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(20,'Sep 21 2009 12:00:00:000AM',4965,5565,2830)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(21,'Aug 24 2009 12:00:00:000AM',15160,16960,8876)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(21,'Aug 31 2009 12:00:00:000AM',2099,2399,1501)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(21,'Sep 7 2009 12:00:00:000AM',14634,16021,7684)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(21,'Sep 14 2009 12:00:00:000AM',2752,3052,1481)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(21,'Sep 21 2009 12:00:00:000AM',2137,2437,1426)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(22,'Aug 24 2009 12:00:00:000AM',6286,6886,3398)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(22,'Aug 31 2009 12:00:00:000AM',2456,2756,1521)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(22,'Sep 7 2009 12:00:00:000AM',4873,5473,2938)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(22,'Sep 14 2009 12:00:00:000AM',10652,11852,6518)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(22,'Sep 21 2009 12:00:00:000AM',17505,19475,10132)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(23,'Aug 25 2008 12:00:00:000AM',2617,2848,1484)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(23,'Aug 24 2009 12:00:00:000AM',2156,2456,1408)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(23,'Aug 31 2009 12:00:00:000AM',5628,6228,3116)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(23,'Sep 7 2009 12:00:00:000AM',4140,4740,2879)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(23,'Sep 14 2009 12:00:00:000AM',6624,7524,4461)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(23,'Sep 21 2009 12:00:00:000AM',1987,2287,1339)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(24,'Aug 25 2008 12:00:00:000AM',2617,2848,1484)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(24,'Aug 31 2009 12:00:00:000AM',4735,5335,2905)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(24,'Sep 7 2009 12:00:00:000AM',2333,2633,1421)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(24,'Sep 14 2009 12:00:00:000AM',10338,11538,6248)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(25,'Aug 25 2008 12:00:00:000AM',5580,6180,3106)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(25,'Aug 24 2009 12:00:00:000AM',5189,5789,2961)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(25,'Aug 31 2009 12:00:00:000AM',10076,11276,6090)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(25,'Sep 7 2009 12:00:00:000AM',6133,7033,4258)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(25,'Sep 14 2009 12:00:00:000AM',6054,6654,2906)INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(25,'Sep 21 2009 12:00:00:000AM',7026,7926,4303)--== Populate Bookstores INSERT INTO [bookstores] ([COMPANYNAME],[DISTRICTNAME],[STOREID],[STORENAME],[STORENBR])VALUES('OnlineDotCom','Memphis','20','Beale','0001')INSERT INTO [bookstores] ([COMPANYNAME],[DISTRICTNAME],[STOREID],[STORENAME],[STORENBR])VALUES('OnlineDotCom','Memphis','21','Crabtree','0002')INSERT INTO [bookstores] ([COMPANYNAME],[DISTRICTNAME],[STOREID],[STORENAME],[STORENBR])VALUES('OnlineDotCom','Tupelo','23','Downtown','0003')INSERT INTO [bookstores] ([COMPANYNAME],[DISTRICTNAME],[STOREID],[STORENAME],[STORENBR])VALUES('OnlineDotCom','Tupelo','24','Jonestreet','0004')INSERT INTO [bookstores] ([COMPANYNAME],[DISTRICTNAME],[STOREID],[STORENAME],[STORENBR])VALUES('OnlineDotCom','Huntsville','25','SpaceCenter','0005')--== The following SELECT gives me the data I need SELECT companyname , districtname , storenbr , businessdate , Mature , Math , AudioBooksfrom booksales sales inner join bookstores stores on stores.storeid = sales.storeid union allSELECT stores.companyname as companyname , stores.districtname as districtname , '' as storenbr , businessDate ,SUM(Mature) as Mature ,Sum(Math) as Math ,Sum(AudioBooks) as AudioBooks from booksales sales inner join bookstores stores on stores.storeid = sales.storeid group by companyname, districtname, businessdate union all SELECT stores.companyname as companyname , '' as districtname , '' as storenbr , CONVERT(VARCHAR(9), businessdate, 6) as businessdate ,SUM(Mature) as Mature ,Sum(Math) as Math ,Sum(AudioBooks) as AudioBooks from booksales sales inner join bookstores stores on stores.storeid = sales.storeid group by companyname, businessdateorder by companyname, districtname, storenbr, businessdate[/code]Records must be in this specific order because end-user must see it this way: OnlineDotCom (Company totals)Memphis (District totals)0001 (storeNbr totals)0002 (storeNbr totals)Tupelo (districtName totals)0003 (storeNbr totals)0004 (storeNbr totals)There is a PDF Spreadsheet attached with the results of the UNION ALL query although results need to be reversed. Challenges I had with my original query: when I rolled up to district, the storenbr was null. This causes the client application to blowup. I tried this in Oracle10g as well thinking that perhaps that the syntactical differences may be causing my problem but was not the case. So for anyone who knows Oracle, I would not mind seeing Oracle version as well.Fri, 20 Nov 2009 01:52:28 GMTTheHTMLDJ