Find the statistical MODE

Transact-SQL
1

Okay, I'm not even certain that MODE is the correct term, but here goes.

Given data as shown in the table below, I want to find the UNIT_PRICE at which the largest total QTY was sold, grouped by PART_ID.

I would also be happy to determine the QTY sold at the lowest UNIT_PRICE and the QTY sold at the highest UNIT_PRICE.

By my calculations, it appears that 19,310 of PART_ID "1" sold at 0.30 each, 110 sold at 0.33 (the highest price) and 175 sold at 0.286 (the lowest price).

How to do this in T-SQL?

PART_ID	UNIT_PRICE	QTY
1	0.2860	175
1	0.2900	80
1	0.2900	200
1	0.2900	800
1	0.2900	500
1	0.2900	200
1	0.2900	100
1	0.2900	250
1	0.3000	2000
1	0.3000	2000
1	0.3000	1500
1	0.3000	1570
1	0.3000	770
1	0.3000	200
1	0.3000	400
1	0.3000	110
1	0.3000	450
1	0.3000	360
1	0.3000	500
1	0.3000	900
1	0.3000	100
1	0.3000	240
1	0.3000	1000
1	0.3000	430
1	0.3000	380
1	0.3000	2000
1	0.3000	4000
1	0.3000	400
1	0.3160	280
1	0.3250	300
1	0.3280	125
1	0.3300	10
1	0.3300	100
2	0.5600	200
3	0.5600	400
3	0.5200	400
3	0.5200	300
3	0.5600	50
3	0.5600	100
4	0.3280	125
4	0.3000	290
4	0.3000	150
4	0.2800	3000
4	0.3000	140
4	0.3000	170
4	0.3000	170
4	0.3000	100
4	0.3300	500
4	0.3400	250
4	0.3000	80
4	0.3700	50
4	0.3250	300
4	0.3000	190
4	0.3000	100
4	0.3400	50
4	0.3000	990
4	0.3280	250
4	0.3600	200
4	0.3250	600
4	0.3100	2500
4	0.3280	200
4	0.3400	250
4	0.3000	420
4	0.3100	3000
4	0.3000	300
4	0.3600	500
4	0.3280	125
4	0.3160	490
4	0.3000	480
4	0.3160	490
4	0.3000	480
4	0.3000	480
4	0.3000	900
4	0.3000	170
4	0.3000	170
4	0.3000	170
4	0.3000	160
4	0.3300	500
4	0.2800	3000
4	0.3000	150
4	0.2860	840
4	0.3000	130
2

We call it T-SQL Aggrates .

Given data as shown in the table below, I want to find the UNIT_PRICE at which the largest total QTY was sold, grouped by PART_ID.

SELECT PART_ID, UNIT_PRICE, SUM(QTY) AS TOTAL_QTY
FROM Your_Table
GROUP BY PART_ID, UNIT_PRICE;

You can limit the number of result by using SELECT TOP 1 but you could also use the function ROW_NUMBER().

1 Like
3 

WITH TotalPartSales AS
(
	SELECT  PART_ID
        ,   UNIT_PRICE
        ,   SUM(QTY) AS TOTAL_QTY
	FROM    PartSales
	GROUP BY PART_ID
        ,   UNIT_PRICE
)
SELECT	PART_ID
	,	UNIT_PRICE AS MODE_UNIT_PRICE
FROM 
(
	SELECT	PART_ID
		,	UNIT_PRICE
		,	TOTAL_QTY
		,	ROW_NUMBER() OVER (PARTITION BY PART_ID ORDER BY TOTAL_QTY DESC) AS rownum
	FROM	TotalPartSales
) ps
WHERE	ps.rownum = 1
	;
1 Like