November 22, 2009 at 1:29 pm
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.
November 22, 2009 at 1:37 pm
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.
November 22, 2009 at 2:05 pm
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.
November 22, 2009 at 4:36 pm
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..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 22, 2009 at 4:42 pm
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..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 22, 2009 at 6:11 pm
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
May 30, 2011 at 9:47 pm
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
August 17, 2011 at 1:16 pm
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
August 18, 2011 at 10:49 am
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