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
What am I doing wrong in my script?
source to share
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 )
source to share
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;
source to share