February 10, 2015 at 9:20 am
I am looking for an elegant way to return top n and sum up the rest, in my case CTE/temp table/variables are not allowed.
Can anyone help? Thank you very much.
February 10, 2015 at 9:27 am
This is a little ambiguous. Can you post an example of what you want?
-- Itzik Ben-Gan 2001
February 10, 2015 at 9:47 am
Alan.B (2/10/2015)
This is a little ambiguous. Can you post an example of what you want?
Thanks for reply and sorry for the ambiguous feeling.
Say I have a select query:
Select Category, Sum(Column1) as Total From MyTable
I need a way to return:
Top n records, plus all the rest records sum up as 'Other' for its Category.
The result should look like:
Category1 Total1
Category2 Total2
Category3 Total3
Category4 Total4
Other sum(Total 1-n) - sum(Total 1-4)
I hope this is clear now.
Thanks.
February 10, 2015 at 10:15 am
Sounds like you need to do your own homework.
February 10, 2015 at 10:27 am
Hint: Use CASE statement for Category and use it in GROUP BY
Far away is close at hand in the images of elsewhere.
Anon.
February 10, 2015 at 10:34 am
David Burrows (2/10/2015)
Hint: Use CASE statement for Category and use it in GROUP BY
Thanks.
Here is what I come up:
select 'Others' as [Category],
sum(
case when [Category] not in
(
select top 5 [Category]
from DailyCategory
Group By
[CATEGORY]
Order By sum(blocks) Desc
) then sum(blocks) else 0 end)
From DailyCategory
I got an error:
Msg 130, Level 15, State 1, Line 15
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
February 10, 2015 at 11:03 am
Why can't you use CTE/temp tables/variables? That's nonsense, next they're going to tell you that you can't use update or select.:hehe:
The solution should be more like this:
SELECT CASE WHEN rn <= 3 THEN Category ELSE 'Others' END,
SUM(blocks)
FROM(
SELECT [Category],
SUM(blocks) blocks,
ROW_NUMBER() OVER(ORDER BY Category) rn
FROM DailyCategory
GROUP BY Category
)t
GROUP BY CASE WHEN rn <= 3 THEN Category ELSE 'Others' END
February 10, 2015 at 11:16 am
Luis Cazares (2/10/2015)
Why can't you use CTE/temp tables/variables? That's nonsense, next they're going to tell you that you can't use update or select.:hehe:The solution should be more like this:
SELECT CASE WHEN rn <= 3 THEN Category ELSE 'Others' END,
SUM(blocks)
FROM(
SELECT [Category],
SUM(blocks) blocks,
ROW_NUMBER() OVER(ORDER BY Category) rn
FROM DailyCategory
GROUP BY Category
)t
GROUP BY CASE WHEN rn <= 3 THEN Category ELSE 'Others' END
Thank you very much Luis
February 10, 2015 at 11:16 am
Here's what I came up with (Assuming this is what you are asking for).
-- SAMPLE DATA
DECLARE @salesbycategory TABLE (category varchar(20) not null, totalamt int not null);
INSERT @salesbycategory
SELECT *
FROM
(VALUES
('category1',10),('category1',15),('category1',20),('category1',25),
('category2',5),('category2',10),('category2',15),('category2',20),
('category3',1),('category3',2),('category3',3),('category3',4)) t(c,ta)
DECLARE @n int = 2;
SELECT category, SUM(totalamt)
FROM
(
SELECT rnk = ROW_NUMBER() over (partition by category order by totalamt DESC), category, totalamt
FROM @salesbycategory
) topn
WHERE rnk <= @n
GROUP by category
UNION ALL
SELECT 'Other', SUM(totalamt)
FROM
(
SELECT rnk = ROW_NUMBER() over (partition by category order by totalamt DESC), category, totalamt
FROM @salesbycategory
) topn
WHERE rnk > @n;
-- Itzik Ben-Gan 2001
February 10, 2015 at 12:06 pm
You're welcome.
It would be nice if you could share with us the reasons behind the restrictions.
February 10, 2015 at 1:07 pm
I'd especially like to understand why a derived table is ok but a CTE is not.
February 11, 2015 at 2:11 am
Just for fun 😀
SELECT ISNULL(Category,'Total') AS [Category],SUM(Column1) AS [Total]
FROM MyTable
GROUP BY Category
WITH ROLLUP
HAVING Category IS NULL OR Category IN ('Category1','Category2','Category3','Category4')
ORDER BY GROUPING(Category) ASC,Category ASC
Far away is close at hand in the images of elsewhere.
Anon.
February 11, 2015 at 1:41 pm
Thanks for replies.
The reason that CTE is not allowed comes from Dundas Dashboard.
I am using the script to create virtual table there. Only Select is allowed, no variable, no CTE, no temp table, no cursor.......
Anyway, glad I got help here, I really appreciate.
February 11, 2015 at 6:21 pm
In the TOP 2 or 3 or 4 you are listing (outside of the Others group), what do you want to do if there are ties?
This looks to me like a TOP 5 customers report or something, and while it is unlikely that you'd get a tie on revenue by customer, it is not entirely inconceivable and you wouldn't want to give a customer short shrift and knock them out of the rankings just because there was coincidentally a tie.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 12, 2015 at 8:36 am
dwain.c (2/11/2015)
In the TOP 2 or 3 or 4 you are listing (outside of the Others group), what do you want to do if there are ties?This looks to me like a TOP 5 customers report or something, and while it is unlikely that you'd get a tie on revenue by customer, it is not entirely inconceivable and you wouldn't want to give a customer short shrift and knock them out of the rankings just because there was coincidentally a tie.
I appreciate your reply, at this moment, there is no need to over complicate the case, but you indicate a very important point and I will definitely keep that in mind later.
Thanks.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply