Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Need help with Grouping and Rollup Expand / Collapse
Author
Message
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
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
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
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
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
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
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
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
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
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 123»»»

Permissions Expand / Collapse