|
|
Posted by csimam on 09/03/07 22:43
On Sep 3, 1:05 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> The best is of course to get all at once in the same result set, since
> then you only need to scan the data once. in SQL 2005 you can achieve
> this with the proprietary extensions WITH CUBE and WITH ROLLUP to
> the GROUP BY clause. SQL 2008, currently in beta, implements GROUPING
> SETS from the ANSI standard, and are a lot more powerful. (They are
> also a lot more confusing to use.)
Thank you Erland for your answer: I spent a bit of time reading about
ROLLUP and CUBE, and found them to be almost exactly what I needed.
But there is one obstacle that I am struggling with:
The data I have, I want to generate totals and subtotals, say of
sales: total sales per store, and subtotal of sales per store per day
for example.
The problem is that I ultimately only want the top 25 stores in total
sales BUT with their subtotals as well... So I used ROLLUP to generate
the totals and subtotals... and in the same query I generated a
ranking of the data... and there I found my problem: the ranking does
not distinguish between totals and subtotals. So the results look
like:
store1, realTotal, 1
store1, subTotal, 2
store2, realTotal, 3
store7, realTotal, 4
store10, subTotal, 5
Clearly, the rows with subtotals should NOT be ranked... but I am
struggling with SQL to figure out how to get exactly what I want.
Mind you, I've already got the problem solved but it involves a nested
query that hits the table twice... and I just wanted to use the
elegancy of ROLLUP to avoid hitting that table twice.
Any ideas?
thanks in advance.
Navigation:
[Reply to this message]
|