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 Saturday, November 21, 2009 12:36 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
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.
Post #822857
Posted Saturday, November 21, 2009 11:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, February 11, 2013 8:27 AM
Points: 1,646, Visits: 1,947
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
Post #822927
Posted Saturday, November 21, 2009 1:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:15 PM
Points: 6,842, Visits: 13,370
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
Post #822934
Posted Sunday, November 22, 2009 2:29 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 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


Post #822963
Posted Sunday, November 22, 2009 11:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:15 PM
Points: 6,842, Visits: 13,370
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
Post #822998
Posted Sunday, November 22, 2009 1:29 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
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.
Post #823009
Posted Sunday, November 22, 2009 1:37 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
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.
Post #823010
Posted Sunday, November 22, 2009 2:05 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
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.


  Post Attachments 
OutputForQuery.pdf (2 views, 55.27 KB)
Post #823014
Posted Sunday, November 22, 2009 4:36 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, February 11, 2013 8:27 AM
Points: 1,646, Visits: 1,947
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
Post #823034
Posted Sunday, November 22, 2009 4:42 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, February 11, 2013 8:27 AM
Points: 1,646, Visits: 1,947
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
Post #823036
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse