Return only the most frequent qty

hermie · March 16, 2020, 3:22pm

SELECT
TOP 1 PART_NBR
,MINOR_MODEL_NBR
,QTY
,COUNT(QTY)
FROM myTable
WHERE MINOR_MODEL_NBR = '8' AND PART_NBR IN ('PN101', 'PN102', 'PN103')
GROUP BY PART_NBR, MINOR_MODEL_NBR, QTY
ORDER BY MostFrequentQTY DESC;

==============================
The code works for one PART_NBR, but have the following issues:

returns only top value for PN101 if I don't have other PART_NBR in the WHERE clause. I have thousands of the PART_NBR, one at a time would be too painful.
say there are 3 MINOR_MODEL_NBR, '8', '9, '10'. Each PART_NBR has is own QTY for its perspective MINOR_MODEL_NBR.
Desired output:
PART_NBR | MINOR_MODEL_NBR | MostFrequentQTY (currently using the Count function with DESC to get this)
Ideally, I would just paste a large list of PART_NBR and get only the desired output described in (3) above

Appreciate all the help with this slow thinker...

mike01 · March 16, 2020, 3:59pm

It's difficult to understand what you are asking for. Can you provide DDL, sample data and then the expected outcome? MostFrequentQty, would suggest a data somewhere, but it looks like you are returning the largest quantity.

hermie · March 16, 2020, 4:02pm

MostFrequentQty is calculated from the Count of Qty for each PART_NBR. Let me see if I put together some sample data.

harishgg1 · March 16, 2020, 5:07pm

Hi hermie ..hope this helps

I replied to your earlier post

I gave an answer which will work here also

Row number
Partition by col 1 col2
Order by qty desc

Where row number = 1

yosiasz · March 16, 2020, 5:14pm

You have 3 posts on this same question. Very hard to follow which one is which.

hermie · March 16, 2020, 5:36pm

This is my 2nd post. First one is going to "unpost" by admin? I saw something on top of first post indicating as such. Besides, the first post was a mess because I try to create a table, rather than learning to solve the problem. Please ignore that post. Thank you.

hermie · March 16, 2020, 5:45pm

Here are records for 'PN101', there are other PN###, as indicated, that will have similar data set. I will try your answer. Thanks.

Thanks.

Row	MINOR_MODEL_NBR	PART_NBR	QTY
1	8	PT101	197
2	9	PT101	38
3	9	PT101	38
4	8	PT101	197
5	8	PT101	197
6	8	PT101	197
7	9	PT101	38
8	9	PT101	38
9	9	PT101	38
10	9	PT101	38
11	9	PT101	38
12	8	PT101	197
13	8	PT101	197
14	8	PT101	197
15	9	PT101	38
16	8	PT101	137
17	9	PT101	38
18	9	PT101	38
19	9	PT101	38
20	9	PT101	38
21	8	PT101	197
22	9	PT101	38
23	8	PT101	197
24	8	PT101	197
25	8	PT101	197
26	9	PT101	38
27	9	PT101	38
28	9	PT101	38
29	9	PT101	38
30	9	PT101	38

yosiasz · March 16, 2020, 6:26pm

Again, please post this as create table and insert table so that we can try it on our sql server?

create table #sample(Row int,	
MINOR_MODEL_NBR int,	
PART_NBR varchar(20),	QTY int)

/*
	PN101 | 9 | 38
	PN101 | 8 | 197
*/
insert into #sample


select 1	,8	,'PT101',	197	union 
select 2	,9	,'PT101',	38	union 
select 3	,9	,'PT101',	38	union 
select 4	,8	,'PT101',	197	union 
select 5	,8	,'PT101',	197	union 
select 6	,8	,'PT101',	197	union 
select 7	,9	,'PT101',	38	union 
select 8	,9	,'PT101',	38	union 
select 9	,9	,'PT101',	38	union 
select 10	,9	,'PT101',	38	union 
select 11	,9	,'PT101',	38	union 
select 12	,8	,'PT101',	197	union 
select 13	,8	,'PT101',	197	union 
select 14	,8	,'PT101',	197	union 
select 15	,9	,'PT101',	38	union 
select 16	,8	,'PT101',	137	union 
select 17	,9	,'PT101',	38	union 
select 18	,9	,'PT101',	38	union 
select 19	,9	,'PT101',	38	union 
select 20	,9	,'PT101',	38	union 
select 21	,8	,'PT101',	197	union 
select 22	,9	,'PT101',	38	union 
select 23	,8	,'PT101',	197	union 
select 24	,8	,'PT101',	197	union 
select 25	,8	,'PT101',	197	union 
select 26	,9	,'PT101',	38	union 
select 27	,9	,'PT101',	38	union 
select 28	,9	,'PT101',	38	union 
select 29	,9	,'PT101',	38	union 
select 30	,9	,'PT101',	38	

;with topsellers
as
(
Select PART_NBR, MINOR_MODEL_NBR,  QTY,
ROW_NUMBER() OVER (
PARTITION BY MINOR_MODEL_NBR, PART_NBR
ORDER BY QTY desc
) row_num
From #sample
)

select PART_NBR, MINOR_MODEL_NBR, QTY
  From topsellers where row_num = 1

drop table #sample

hermie · March 16, 2020, 6:28pm

harishgg1,
not quite follow your answer. The two critical element in the where clause, minor_model_nbr and part_nbr, will have a different most frequent QTY. In the sample dataset below, output should be:
part_nbr | minor_model_nbr | qty
PN101 | 9 | 38
PN101 | 8 | 197

And, I only want this 2 results, not the 3 row with "137" as the qty. Note that 38 QTY came up 18 time, aka most frequent, similarly with 197 which has appeared 11 time. Other combinations of part_nbr and minor_model_number will have different qty. The code should only capture the most frequently appeared qty. The following gives the desired output for one part_nbr & minor_model_nbr combination:

SELECT	TOP 1 PART_NBR
	,MINOR_MODEL_NBR
	,QTY
	,COUNT(QTY) AS MostFrequent
FROM	myTable
WHERE  MINOR_MODEL_NBR = '8' and PART_NBR in ('PN101')
GROUP BY PART_NBR, MINOR_MODEL_NBR, QTY
ORDER BY MostFrequent DESC;

hope you understand my problem.

mike01 · March 16, 2020, 7:01pm

Using Yosiasz's data.

create table #sample(Row int,	
MINOR_MODEL_NBR int,	
PART_NBR varchar(20),	QTY int)

/*
	PN101 | 9 | 38
	PN101 | 8 | 197
*/
insert into #sample


select 1	,8	,'PT101',	197	union 
select 2	,9	,'PT101',	38	union 
select 3	,9	,'PT101',	38	union 
select 4	,8	,'PT101',	197	union 
select 5	,8	,'PT101',	197	union 
select 6	,8	,'PT101',	197	union 
select 7	,9	,'PT101',	38	union 
select 8	,9	,'PT101',	38	union 
select 9	,9	,'PT101',	38	union 
select 10	,9	,'PT101',	38	union 
select 11	,9	,'PT101',	38	union 
select 12	,8	,'PT101',	197	union 
select 13	,8	,'PT101',	197	union 
select 14	,8	,'PT101',	197	union 
select 15	,9	,'PT101',	38	union 
select 16	,8	,'PT101',	137	union 
select 17	,9	,'PT101',	38	union 
select 18	,9	,'PT101',	38	union 
select 19	,9	,'PT101',	38	union 
select 20	,9	,'PT101',	38	union 
select 21	,8	,'PT101',	197	union 
select 22	,9	,'PT101',	38	union 
select 23	,8	,'PT101',	197	union 
select 24	,8	,'PT101',	197	union 
select 25	,8	,'PT101',	197	union 
select 26	,9	,'PT101',	38	union 
select 27	,9	,'PT101',	38	union 
select 28	,9	,'PT101',	38	union 
select 29	,9	,'PT101',	38	union 
select 30	,9	,'PT101',	38

Does this generate what you want??

select PART_NBR, MINOR_MODEL_NBR, QTY, N
  from (
  Select PART_NBR, MINOR_MODEL_NBR, QTY, count(1) as N, 
		Row_Number() over (partition by PART_NBR, MINOR_MODEL_NBR order by  PART_NBR, MINOR_MODEL_NBR, count(1) desc) as RowNum
  from #Sample
  group by PART_NBR, MINOR_MODEL_NBR, QTY) x
where RowNum = 1

hermie · March 16, 2020, 7:31pm

Mike01,

This is exactly, exactly what I am after!! For the list of Part_Nbr, I just tag on the PART_NBR IN quotes, and I am in business.

Now, on my own, I will never be able to come up with that even some of the hints harishgg1 gave me. I studied "over" and "partition by" and Row_Number(). I need to understand the logic as I have a very similar situation, but the results need to join another table. I would like to be able to come up with that on my own.

Could you please point me to some resources on some of the things you did here? My first approach was a sub-query, like of like your what you have, but soon I ran into issue not being able to pass the Part_Nbr out. The examples I saw were by Wise Owl on youtube with static parameters.

Thanks again!
Hermie

mike01 · March 16, 2020, 7:35pm

Is this a procedure or function you are calling? How are you calling this or how is it being used?

hermie · March 16, 2020, 8:02pm

It is being used as is, a procedure I guess. I just have a large table with over 360k of Part_Nbr and depends on the Minor_Model_Nbr. Most of the time, I am interested in a few thousand parts with its Qty. The other table has Part_Nbr attributes like name and serial number, etc. Straight forward to join the tables, I better learn the logic with what you did. Could I please trouble you to put a few comment lines what say this does? Is Count(1) the same as Top 1, but at column level, Top 1 is at row level?

Row_Number() over (partition by PART_NBR, MINOR_MODEL_NBR order by PART_NBR, MINOR_MODEL_NBR, count(1) desc) as RowNum

hermie · March 16, 2020, 9:13pm

I think I am getting this...the Count(1) returns the one item of each partitioned groups. Since DESC, it returns the large count, N.
" COUNT ( 1 ) returns the number of items in a group. This includes NULL values and duplicates. COUNT (ALL expression) evaluates expression for each row in a group and returns the number of nonnull values."

I have tried it with a larger Part_Nbr list with more than 2 Minor_Model_Nbr, all showed!

Now, what does the RowNum = 1 do? I tried changing RowNum = 2 and count(2), I don't get 1st and 2nd hightest 2 qty back with respect to the Part_Nbr.

mike01 · March 16, 2020, 11:59pm

Hermie, I'll explain below, but Count(1) is the same as Count(). There was a time when Count() was slower that count(1), so we started using count(1) (Old habits). Anyway.....

The inner query is grouping and assigning row numbers to each record. The grouping is by Part_nbr, Minor_model_nbr and Qty. This will give you counts of each quantity. The Row_number is assigning row numbers to each row within the partition, So, if a Part_nbr or Minor_model_nbr changes it will restart the numbering by 1. The order by puts it in order by Part_nbr, Minor_model_nbr and then the count in descending order. By not adding the QTY column here, it will give me the largest counts by keys first. Knowing the the first record in each partition will be the largest count, the outer query selects from this where the rownum = 1. If you just run the inner query, you'll see how the row numbering works. Also, we use temp tables (#sample) in examples here because they go away when we close the session, so we don't have to worry about cluttering up our databases. That's why it's important to setup DDL, sample data and expected results. As Yosiasz says, if you aren't going to give us access to your system, then you need to provide us with something to help. Hope this explanation helps

  Select PART_NBR, MINOR_MODEL_NBR, QTY, count(1) as N, 
		Row_Number() over (partition by PART_NBR, MINOR_MODEL_NBR order by  PART_NBR, MINOR_MODEL_NBR, count(1) desc) as RowNum
  from #Sample
  group by PART_NBR, MINOR_MODEL_NBR, QTY

hermie · March 19, 2020, 3:36am

Thanks very much for the explanation. You have "taught a man to fish." I will do better to come up with sample data, and expected result in the future when I post questions.