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
4

hi

hope this helps

create table insert data script 
CREATE TABLE sales_line_items (
  PART_ID INT NOT NULL,
  UNIT_PRICE DECIMAL(10, 4) NOT NULL,
  QTY INT NOT NULL
);

INSERT INTO sales_line_items (PART_ID, UNIT_PRICE, QTY) VALUES
(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);

image

; WITH cte AS  
          ( SELECT part_id, unit_price, sum(qty) as sm FROM  sales_line_items GROUP  BY part_id,unit_price ) 
  ,    cte_rn AS 
          ( SELECT  ROW_NUMBER() OVER( PARTITION  by part_id  ORDER BY sm DESC ) as rn ,* FROM cte )
  SELECT  * FROM  cte_rn WHERE rn  = 1

image
image350×149 19.5 KB

image

; with cte as 
	( SELECT part_id, unit_price, sum(qty) as sm FROM  sales_line_items GROUP  BY part_id,unit_price 
	) 
, cte_ok as 
	(select part_id,min(unit_price) as min_max from cte group by part_id
	union all 
	select part_id,max(unit_price) from cte group by part_id
	)
select 
     distinct cte_ok.*,cte.sm 
from 
      cte_ok join cte on cte_ok.part_id= cte.part_id and cte_ok.min_max = cte.unit_price
order by 
      part_id , min_max

image
image296×230 25.2 KB