Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 7,2000 » T-SQL » Need help with Grouping and Rollup
Add to briefcase
24 posts, Page 1 of 3
123»»»

Need help with Grouping and Rollup Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
TheHTMLDJ
Posted Friday, November 20, 2009 1:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 17, 2011 2:42 AM
Points: 46, Visits: 179
This 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 have
a 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 need

It 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



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
      , AudioBooks
from booksales sales  
inner join bookstores stores on stores.storeid = sales.storeid  
union all
SELECT  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, businessdate
order by companyname, districtname, storenbr, businessdate


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.

  Post Attachments 
OutputForQuery.pdf (8 views, 55.27 KB)
Post #822159
arun.sas
Posted Friday, November 20, 2009 8:27 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,231, Visits: 3,483
Hi,
Post the schema of the booksales table, its helpful us to understand better.
Post #822817
Bru Medishetty
Posted Friday, November 20, 2009 9:14 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, February 11, 2013 8:27 AM
Points: 1,735, Visits: 1,947
Used the code given by you. Still not complete code.. at least as of now....

I get the following error 

Msg 207, Level 16, State 1, Line 29
Invalid column name 'week_date'.
Msg 207, Level 16, State 1, Line 34
Invalid column name 'weeknbr'.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.com\LearnSQLWithBru

Twitter -- BruMedishetty
Post #822824
TheHTMLDJ
Posted Friday, November 20, 2009 11:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 17, 2011 2:42 AM
Points: 46, Visits: 179
the select statement should be: 
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.storeid
group by          businessdate 
             ,    stores.companyname
             ,    stores.districtname
             ,    STORES.STORENBR
             ,    STORES.STORENAME            
Order By businessdate, storenbr
Post #822841
TheHTMLDJ
Posted Friday, November 20, 2009 11:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 17, 2011 2:42 AM
Points: 46, Visits: 179
Post the schema of the booksales table


Do you mean my first table definition?
Post #822844
arun.sas
Posted Friday, November 20, 2009 11:26 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,231, Visits: 3,483
TheHTMLDJ (11/20/2009)
Post the schema of the booksales table

Do you mean my first table definition?


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
Order By DISTRICTNAME,storenbr ,businessdate

Post #822846
TheHTMLDJ
Posted Friday, November 20, 2009 11:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 17, 2011 2:42 AM
Points: 46, Visits: 179
arun.sas (11/20/2009)
TheHTMLDJ (11/20/2009)
Post the schema of the booksales table

Do you mean my first table definition?


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
Order By DISTRICTNAME,storenbr ,businessdate



Sorry, we must have crossed paths.
Make the order companyname, districtname, storenbr, businessdate
Post #822850
arun.sas
Posted Friday, November 20, 2009 11:46 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,231, Visits: 3,483
TheHTMLDJ (11/20/2009)
arun.sas (11/20/2009)
TheHTMLDJ (11/20/2009)
Post the schema of the booksales table

Do you mean my first table definition?


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
Order By DISTRICTNAME,storenbr ,businessdate



Sorry, we must have crossed paths.
Make the order companyname, districtname, storenbr, businessdate


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?
Post #822851
TheHTMLDJ
Posted Friday, November 20, 2009 11:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 17, 2011 2:42 AM
Points: 46, Visits: 179

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?


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.
Post #822852
arun.sas
Posted Saturday, November 21, 2009 12:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,231, Visits: 3,483
TheHTMLDJ (11/20/2009)Im not sure what you meant by what formats need to be retrieved.

I mean

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)

is now retrieved correctly?
Post #822854
« Prev Topic | Next Topic »

Add to briefcase
24 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.