Need help with Grouping and Rollup

  • lmu92 (11/22/2009)


    It looks like either your result set does not match your requirement or your requirement is still unclear...

    Example:

    companynamedistrictnamestorenbrbusinessdateMatureMathAudioBooks

    OnlineDotComHuntsville00052009-09-07 00:00:00.00017521752360

    OnlineDotComHuntsville00052009-09-07 00:00:00.00023882388519

    OnlineDotComHuntsville00052009-09-07 00:00:00.00019931993379

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

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

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

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

    Twitter -- BruMedishetty

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

    Twitter -- BruMedishetty

  • WOW!!! This is my exactly my point. Another post where there was no answer but a lecture.

    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

    Sorry but I feel it is better for someone linking to this thread from Google that they not have to go through lots of clarifications. It will turn them off before getting to potentially valuable knowledge.

    I was referring to topics such as Ranse, Dense_Ranks, Patitioning, Windows

  • Think of not using any of the first three columns, and instead constructing the column you want;

    use the grouping function to build that new column and also to contrive the sequence you want:

    Something like:

    select case

    when grouping(companyname)=1 then 'Co: ' + companyName

    when grouping(districtname)=1 then 'Dist: ' + districtname

    else 'Store: ' + StoreNbr end as 'Column 1'

    --, companyname, districtname, storenbr

    , sum( Mature) Mature

    , sum( Math) Math

    , sum(AudioBooks) Audio

    from bookstores bs

    inner join booksales sls

    on sls.storeid = bs.storeId

    -- where sls.BusinessDate ... (I presume)

    group by companyname, districtname, storeNbr

    with rollup

    order by grouping(companyname) desc

    , grouping(districtname) desc

    , storeNbr asc

  • I am trying to a non aggregate rollup... sort of.

    I have columns for student, term1, term2, term3, etc.

    The data under term is actually a course that is being taken that term so there can be multiple rows for a student. I create the query dynamically based on a date parameter. All of this works fine except I am getting duplicated rows that I can't get rid of using DISTINCT. I am POSITIVE every one of the columns is identical to another row. I have tried using isnull around every data column to make sure i have a blank string in the column (in the back of my head I always have the little voice that says "a NULL may not be equal to another NULL").

    Now the REALLY UGLY part. Typically I'm going beyond 32 columns so SQL throws up with the can't have more than 32 groups error. I can do this manually in Excel but thats manual. I don't like doing things twice or even having to use Excel to manipulate data.

    Any ideas? I can't really post the code as the company I work for is really paranoid about things getting posted publicly.

    TIA,

    jb

  • JB, I would recommend starting your own thread to ask for help, instead of posting in a 2 year old thread.

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply