Quarter a table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Quarter a table

Author

Topic

dmilam
Posting Yak Master

185 Posts

Posted - 2011-10-06 : 18:43:28

Not 25% of successive results like here, but 25% of the base table so that it's split evenly in four (assigning to a fourth). How to? There must be some simpler solution that I'm missing.



DECLARE @quartile TABLE (
CustomerID INT NOT NULL UNIQUE,
Quartile INT NOT NULL
);

DECLARE @i TINYINT = 1;

WHILE @i < 5

BEGIN

INSERT @quartile ( CustomerID, Quartile )

SELECT DISTINCT TOP 25 PERCENT T.CustomerID, [Quartile] = @i
FROM #Temp AS T
WHERE NOT EXISTS (
SELECT 1
FROM @quartile q
WHERE q.CustomerID = T.CustomerID
)
;

SET @i = @i + 1

END ;

SELECT Quartile, COUNT(CustomerID)
FROM @quartile
GROUP BY Quartile

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-10-06 : 18:52:30

Lookup NTILE in BOL and you'll find what you're looking for.

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

dmilam
Posting Yak Master

185 Posts

Posted - 2011-10-06 : 19:10:58

quote:
Originally posted by Bustaz Kool

Lookup NTILE in BOL and you'll find what you're looking for.

Will do, thanks!

dmilam
Posting Yak Master

185 Posts

Posted - 2011-10-06 : 19:37:50

NTILE is it, thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources