December 18, 2014 at 2:27 am
Hi I have table
CREATE TABLE #RESULT
(
Name Varchar(32),
Quantity Int,
Volume Float
)
insert into #RESULT values('alan',30,323232.23)
insert into #RESULT values('Neil',1,32)
insert into #RESULT values('Baron',1,32)
insert into #RESULT values('Claire',10,3234545)
insert into #RESULT values('Tom',2,455644.23)
insert into #RESULT values('Foo',4,343334)
I want to Group the #result data in a way that all the Volume which is less than 3% should be grouped in others
I have done this much so far
DECLARE @Total FLOAT
DECLARE @OtherVolumePercentage FLOAT
SET @Total = (SELECT SUM(Volume) from #RESULT)
SET @OtherVolumePercentage = 3 * (@Total/100)
Now I want to some how check in the select if Volume > OtherVolumePercentage then Name else Others
So the data should look something like
Name Quantity Volume
Alan 30 323232.23
Claire 10 3234545
Tom 2 455644.23
Foo 4 4343334
Others 2 64
So Neil and Baron are categoried in others
Thanks
December 18, 2014 at 2:36 am
This should do the trick:
SELECT Name,
SUM(Quantity) AS SumQuantity,
SUM(Volume) AS SumVolume
FROM (
SELECT CASE WHEN SUM(Volume) OVER() * .03 < Volume THEN Name ELSE 'Others' END AS Name,
Quantity,
Volume
FROM #RESULT
) AS data
GROUP BY Name
ORDER BY SumVolume DESC
-- Gianluca Sartori
December 18, 2014 at 3:09 am
thanks..
is there a better way of doing it ..instead of using a subquery???
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply