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.