Clever Geek Handbook

How to select the last 25% of a dataset

The question asked to us: "Show all clear orders where the quantity is in the lower quartile."

I tried the following

USE [Northwind]
GO

DECLARE @maxValue int
SELECT @maxValue = MAX(Quantity) FROM [Order Details]

SELECT OrderID, ProductID, (Quantity / @maxValue) AS 'tot'
FROM [Order Details] AS od
WHERE 'tot' <= 0.25
ORDER BY ProductID
GO

the output should look like this

output should look like this

What am I doing wrong in my script?

+3


source to share


4 answers


You can convert it to first DECIMAL

, and then do the match Something like this.((Quantity *1.0) / @maxValue) 

DECLARE @maxValue DECIMAL(18,2)
SELECT @maxValue = MAX(Quantity) FROM [Order Details]
SELECT OrderID, ProductID, ((Quantity *1.0) / @maxValue) AS 'tot'
FROM [Order Details] AS od
WHERE ((Quantity *1.0) / @maxValue) <= 0.25
ORDER BY ProductID


You can optionally use a sentence WHERE

like thisWHERE Quantity <= (@maxValue * 0.25 )

+1


source


Have you tried transitioning (Quantity / @maxValue)<=0.25

instead of 'tot'?



+1


source


I would use WINDOWED functions

to achieve this. You don't have to query the [Order Details] table twice. Try:

SELECT *
FROM (
    SELECT od.OrderID, od.ProductID, ((od.Quantity * 1.00) / MAX(od.Quantity) OVER())) AS tot
    FROM [Order Details] AS od
    ) AS T
WHERE T.tot <= 0.25
ORDER BY T.ProductID;

But your description says you are looking for the bottom quartile, which makes me think what NTILE()

would be the best way for you:

SELECT *
FROM (
    SELECT od.OrderID, od.ProductID, NTILE(4) OVER (ORDER BY od.Quantity) AS Quartile
    FROM [Order Details] AS od
    ) AS T
WHERE T.Quartile = 1
ORDER BY T.ProductID;
0


source


I found a solution to my problem.

USE Northwind
GO

SELECT DISTINCT a.OrderID, a.ProductID
FROM [Order Details] as a
WHERE Quantity <= (SELECT AVG(Quantity * 0.25
             FROM [Order Details]
             WHERE a.ProductID = ProductID)
GO
0


source






More articles:


All Articles