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
arun.sas (11/21/2009)
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?


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.
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: 2526 Visits: 1950
Hi,

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

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)

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.


Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.com\LearnSQLWithBru

Twitter -- BruMedishetty
LutzM
LutzM
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10399 Visits: 13559
If 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:

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))



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 ...



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
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 following query gives me exactly what I need except the companyname and districtname columns need to be eliminated
1) when the districtname is empty, the companyname becomes the districtname
2) 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


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
, 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



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
LutzM
LutzM
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10399 Visits: 13559
It looks like either your result set does not match your requirement or your requirement is still unclear...
Example:
companyname districtname storenbr businessdate Mature Math AudioBooks
OnlineDotCom Huntsville 0005 2009-09-07 00:00:00.000 1752 1752 360
OnlineDotCom Huntsville 0005 2009-09-07 00:00:00.000 2388 2388 519
OnlineDotCom 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.
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)

But if i can get data in correct sort order, I can avoid doing a dataview in my .NET program. Not important.

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)

I will ultimately have to convert this over to an Oracle procedure which has different syntax but T-SQL will work for now.

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 and
c) for Oracle instead of SQL Server
I'm outta here. Have fun, whoever stays in here...



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
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
lmu92 (11/22/2009)
It looks like either your result set does not match your requirement or your requirement is still unclear...
Example:
companyname districtname storenbr businessdate Mature Math AudioBooks
OnlineDotCom Huntsville 0005 2009-09-07 00:00:00.000 1752 1752 360
OnlineDotCom Huntsville 0005 2009-09-07 00:00:00.000 2388 2388 519
OnlineDotCom 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.



Oversight on my part in preparing test data but should not matter. Will just have double the amounts for that day.


You also didn't state on how the business date column needs to be sorted....please have a look at BOL



I tried this and got interesting date error:

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



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.

You also haven't answer my question regarding the reason to order it by storenbr.


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.

I'm outta here. Have fun, whoever stays in here...


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.
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
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.
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
A 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.
Attachments
OutputForQuery.pdf (6 views, 55.00 KB)
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: 2526 Visits: 1950
TheHTMLDJ (11/22/2009)
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.


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..


Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.com\LearnSQLWithBru

Twitter -- BruMedishetty
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: 2526 Visits: 1950
Hi,

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..


Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.com\LearnSQLWithBru

Twitter -- BruMedishetty
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