SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help with Grouping and Rollup


Need help with Grouping and Rollup

Author
Message
TheHTMLDJ
TheHTMLDJ
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
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.
Attachments
OutputForQuery.pdf (18 views, 55.00 KB)
arun.sas
arun.sas
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1655 Visits: 3493
Hi,
Post the schema of the booksales table, its helpful us to understand better.
Bru Medishetty
Bru Medishetty
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2502 Visits: 1950
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
TheHTMLDJ
TheHTMLDJ
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
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


TheHTMLDJ
TheHTMLDJ
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 179
Post the schema of the booksales table


Do you mean my first table definition?
arun.sas
arun.sas
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1655 Visits: 3493
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 


TheHTMLDJ
TheHTMLDJ
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
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
arun.sas
arun.sas
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1655 Visits: 3493
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?
TheHTMLDJ
TheHTMLDJ
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
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.
arun.sas
arun.sas
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1655 Visits: 3493
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search