SQLTeam.com | Weblogs | Forums

Nested query needed?

Greetings,

New to SQL, trying to get results of a table that looks like the following:

I would to have the query return the most frequent quantity ordered by Part Type, Model Type, most frequent Qty. The following is my work-around. Post process need to use Vlookup to pick the correct result. Obvisously, I have thousands of RXXXX. Now, I have to do 3 runs by switch it to "B" and "C" in the statement. There's got to be a better way.

Select Part_Type, Model_Type, Qty, Count(Qty) as Frequent_Order
From Cust_Tbl
Where Model_Type = 'A' and Part_Type in ('R01', 'R02', 'R03',...'R20')
Group by Part_Type, Model_Type, Qty
Order by Frequent_Order DESC

I would dump this table in Excel, Vlookup sort this out.

Now, I played with just having it return the top row with TOP 1 function below, but it only return the first R01. How do I dynamically pass R02, R03, etc and "B" and "C" into this query, so it will return the most frequent order quantity for the R01, R02, etc.

Select TOP 1 Count(Qty) as Frequent_Order
From Cust_Tbl
Where Model_Type = "A" and Part_Type in ('R01', 'R02', 'R03',...'R20')
Order by Frequent_Order DESC

Thank you very much.
Hermie

Sample table below for reference:

SQL Sample table

Welcome to sql forum. to help us help you please provide the sample data as follows and not as an image?

--this is just sample data as we do not have 
--access to your Cust_Tbl table
create table #Cust_Tbl(item int, Part_Type char(3), Model_type char(3), Customer char(3), qty int)

insert into #Cust_Tbl
select 1, 'R01', 'A', 'N01', 1 union
select 1, 'R01', 'A', 'N02', 2 union
select 1, 'R01', 'BB', 'N03', 2 union
select 1, 'R01', 'BB', 'N04', 2 union
select 1, 'R01', 'A', 'N05', 3 union
select 1, 'R01', 'A', 'N06', 3 union
select 1, 'R12', 'A', 'N07', 3 

;with topsellers
as
(
Select Part_Type, Model_Type, Qty,item,
ROW_NUMBER() OVER (
      PARTITION BY item
      ORDER BY Qty desc
   ) row_num
From #Cust_Tbl 
)

select * From topsellers where row_num = 1

--select * From #Cust_Tbl

drop table #Cust_Tbl

Does this work?

Item Party Type Model Type Customer Qty
1 R01 A Cus101 1
2 R01 A Cus102 2
3 R01 A Cus103 2
4 R01 A Cus104 2
5 R01 A Cus105 3
6 R01 A Cus106 3
7 R01 BB Cus107 12
8 R01 BB Cus108 15
9 R01 BB Cus109 16
10 R01 BB Cus110 16
11 R02 BB Cus111 12
12 R02 BB Cus112 16
13 R02 BB Cus113 150
14 R02 BB Cus114 155
15 R02 BB Cus115 155
16 R03 BB Cus116 155
17 R03 BB Cus117 160
18 R03 BB Cus118 160
19 R03 BB Cus119 150
20 R03 CCC Cus120 160
21 R04 CCC Cus121 170
22 R01 CCC Cus122 4
23 R01 CCC Cus123 4
24 R01 CCC Cus124 4
25 R01 CCC Cus125 5
26 R01 CCC Cus126 6
27 R01 CCC Cus127 7
28 R01 CCC Cus128 8
29 R04 CCC Cus129 55
30 R04 CCC Cus130 50

...Looks like it will need a CTE. Trying to learn the logic. It isn't obvious to me yet. I will have to log into work and get some sample data. Thanks for your reply.

It could but if you put it the way I have laid it out you will get faster help

Create table #sample

Insert into #sample

You really do not need to log into work. Just follow the example I gave you

here is an example of one part type:

CURRENT_CUST_CD	MAJOR_ACCOUNT	MINOR_ACCOUNT	ACCOUNT_SERIAL_NBR	CUST_ORDER_NO	CUST_ACCT_STATUS	REVISION_DT	PART_TYPE	QTY_CUST_ORDER

1 ABC 123 1 38,619 CON123 Active 3/14/2020 PT110 197
2 ABC 123 2 38,629 CON124 Active 3/14/2020 PT110 38
3 ABC 123 2 38,618 CON125 Active 3/14/2020 PT110 38
4 ABC 123 1 38,614 CON126 Active 3/14/2020 PT110 197
5 ABC 123 1 60,629 CON127 Active 3/14/2020 PT110 197
6 ABC 123 1 38,609 CON128 Active 3/14/2020 PT110 197
7 ABC 123 2 38,622 CON129 Active 3/14/2020 PT110 38
8 ABC 123 2 38,616 CON130 Active 3/14/2020 PT110 38
9 ABC 123 2 60,627 CON131 Active 3/14/2020 PT110 38
10 ABC 123 2 38,617 CON132 Active 3/14/2020 PT110 38
11 ABC 123 2 38,626 CON133 Active 3/14/2020 PT110 38
12 ABC 123 1 38,615 CON134 Active 3/14/2020 PT110 197
13 ABC 123 1 38,611 CON135 Active 3/14/2020 PT110 197
14 ABC 123 1 38,612 CON136 Active 3/14/2020 PT110 197
15 ABC 123 2 38,627 CON137 Active 3/14/2020 PT110 38
16 ABC 123 1 38,610 CON138 Active 3/14/2020 PT110 137
17 ABC 123 2 38,630 CON139 Active 3/14/2020 PT110 38
18 ABC 123 2 38,628 CON140 Active 3/14/2020 PT110 38
19 ABC 123 2 38,632 CON141 Active 3/14/2020 PT110 38
20 ABC 123 2 38,621 CON142 Active 3/14/2020 PT110 38
21 ABC 123 1 60,631 CON143 Active 3/14/2020 PT110 197
22 ABC 123 2 38,631 CON144 Active 3/14/2020 PT110 38
23 ABC 123 1 38,613 CON145 Active 3/14/2020 PT110 197
24 ABC 123 1 60,626 CON146 Active 3/14/2020 PT110 197
25 ABC 123 1 60,630 CON147 Active 3/14/2020 PT110 197
26 ABC 123 2 38,625 CON148 Active 3/14/2020 PT110 38
27 ABC 123 2 60,628 CON149 Active 3/14/2020 PT110 38
28 ABC 123 2 38,624 CON150 Active 3/14/2020 PT110 38
29 ABC 123 2 38,623 CON151 Active 3/14/2020 PT110 38
30 ABC 123 2 38,620 CON152 Active 3/14/2020 PT110 38

Please follow the sample script I provided you and post back.

create table #Cust_Tbl(item int, Part_Type char(3), Model_type char(3), Customer char(3), qty int)

insert into #Cust_Tbl
Not gonna do all that work for you.

applying your code, I only get one record back

PART_TYPE, MINOR_ACCOUNT, QTY_CUST_ORDER, freq_COUNT
PT110, 1, 197, 1

;WITH top_qty_cnt
as
(SELECT part_type, minor_account, qty_order,
ROW_NUMBER() OVER (
PARTITION BY part_type
ORDER BY qty_order DESC
) freq_count
FROM CUST_TBL
WHERE part_type in ('PT110')
)
SELECT * FROM top_qty_cnt WHERE freq_count = 1

==============

desire result

PART_TYPE, MINOR_ACCOUNT, QTY_CUST_ORDER, freq_COUNT
PT110, 2, 38, 18
PT110, 1, 197, 11

additional info: I have a total of only 3 minor account type, e.g. 1,2,3
and have 2000 part_types.

Many Thanks!
Hermie

Please provide your sample data in the format I indicated

create table #Cust_Tbl(item int, Part_Type char(3), Model_type char(3), Customer char(3), qty int)

insert into #Cust_Tbl

sorry got a syntax error with insert. Don't know if this helps

CURRENT_CUST	MAJOR_ACCT	MINOR_ACCT	ACCT_SERIAL_NBR	CUST_ORDER_NO	CUST_ACCT_STATUS	REVISION_DT	PART_TYPE	QTY_CUST_ORDER		

1 ABC 123 1 38,619 CON123 Active 3/14/2020 PT110 197 '1','ABC','123',1','38619','CON123','Active','43904','PT110'
2 ABC 123 2 38,629 CON124 Active 3/14/2020 PT110 38 '2','ABC','123',2','38629','CON124','Active','43904','PT110'
3 ABC 123 2 38,618 CON125 Active 3/14/2020 PT110 38 '3','ABC','123',2','38618','CON125','Active','43904','PT110'
4 ABC 123 1 38,614 CON126 Active 3/14/2020 PT110 197 '4','ABC','123',1','38614','CON126','Active','43904','PT110'
5 ABC 123 1 60,629 CON127 Active 3/14/2020 PT110 197 '5','ABC','123',1','60629','CON127','Active','43904','PT110'
6 ABC 123 1 38,609 CON128 Active 3/14/2020 PT110 197 '6','ABC','123',1','38609','CON128','Active','43904','PT110'
7 ABC 123 2 38,622 CON129 Active 3/14/2020 PT110 38 '7','ABC','123',2','38622','CON129','Active','43904','PT110'
8 ABC 123 2 38,616 CON130 Active 3/14/2020 PT110 38 '8','ABC','123',2','38616','CON130','Active','43904','PT110'
9 ABC 123 2 60,627 CON131 Active 3/14/2020 PT110 38 '9','ABC','123',2','60627','CON131','Active','43904','PT110'
10 ABC 123 2 38,617 CON132 Active 3/14/2020 PT110 38 '10','ABC','123',2','38617','CON132','Active','43904','PT110'
11 ABC 123 2 38,626 CON133 Active 3/14/2020 PT110 38 '11','ABC','123',2','38626','CON133','Active','43904','PT110'
12 ABC 123 1 38,615 CON134 Active 3/14/2020 PT110 197 '12','ABC','123',1','38615','CON134','Active','43904','PT110'
13 ABC 123 1 38,611 CON135 Active 3/14/2020 PT110 197 '13','ABC','123',1','38611','CON135','Active','43904','PT110'
14 ABC 123 1 38,612 CON136 Active 3/14/2020 PT110 197 '14','ABC','123',1','38612','CON136','Active','43904','PT110'
15 ABC 123 2 38,627 CON137 Active 3/14/2020 PT110 38 '15','ABC','123',2','38627','CON137','Active','43904','PT110'
16 ABC 123 1 38,610 CON138 Active 3/14/2020 PT110 137 '16','ABC','123',1','38610','CON138','Active','43904','PT110'
17 ABC 123 2 38,630 CON139 Active 3/14/2020 PT110 38 '17','ABC','123',2','38630','CON139','Active','43904','PT110'
18 ABC 123 2 38,628 CON140 Active 3/14/2020 PT110 38 '18','ABC','123',2','38628','CON140','Active','43904','PT110'
19 ABC 123 2 38,632 CON141 Active 3/14/2020 PT110 38 '19','ABC','123',2','38632','CON141','Active','43904','PT110'
20 ABC 123 2 38,621 CON142 Active 3/14/2020 PT110 38 '20','ABC','123',2','38621','CON142','Active','43904','PT110'
21 ABC 123 1 60,631 CON143 Active 3/14/2020 PT110 197 '21','ABC','123',1','60631','CON143','Active','43904','PT110'
22 ABC 123 2 38,631 CON144 Active 3/14/2020 PT110 38 '22','ABC','123',2','38631','CON144','Active','43904','PT110'
23 ABC 123 1 38,613 CON145 Active 3/14/2020 PT110 197 '23','ABC','123',1','38613','CON145','Active','43904','PT110'
24 ABC 123 1 60,626 CON146 Active 3/14/2020 PT110 197 '24','ABC','123',1','60626','CON146','Active','43904','PT110'
25 ABC 123 1 60,630 CON147 Active 3/14/2020 PT110 197 '25','ABC','123',1','60630','CON147','Active','43904','PT110'
26 ABC 123 2 38,625 CON148 Active 3/14/2020 PT110 38 '26','ABC','123',2','38625','CON148','Active','43904','PT110'
27 ABC 123 2 60,628 CON149 Active 3/14/2020 PT110 38 '27','ABC','123',2','60628','CON149','Active','43904','PT110'
28 ABC 123 2 38,624 CON150 Active 3/14/2020 PT110 38 '28','ABC','123',2','38624','CON150','Active','43904','PT110'
29 ABC 123 2 38,623 CON151 Active 3/14/2020 PT110 38 '29','ABC','123',2','38623','CON151','Active','43904','PT110'
30 ABC 123 2 38,620 CON152 Active 3/14/2020 PT110 38 '30','ABC','123',2','38620','CON152','Active','43904','PT110'

using sql assistant. table are generated using excel and paste into sql assistant

Still have some issues with the quality of the data. Missing single quotes etc

Without any good sample data it would just be a back and forth guess work
Try this tool

http://sqlfiddle.com/

You are right...

'1','ABC','123','1','38619','CON123','Active','43904','PT110'
'2','ABC','123','2','38629','CON124','Active','43904','PT110'
'3','ABC','123','2','38618','CON125','Active','43904','PT110'
'4','ABC','123','1','38614','CON126','Active','43904','PT110'
'5','ABC','123','1','60629','CON127','Active','43904','PT110'
'6','ABC','123','1','38609','CON128','Active','43904','PT110'
'7','ABC','123','2','38622','CON129','Active','43904','PT110'
'8','ABC','123','2','38616','CON130','Active','43904','PT110'
'9','ABC','123','2','60627','CON131','Active','43904','PT110'
'10','ABC','123','2','38617','CON132','Active','43904','PT110'
'11','ABC','123','2','38626','CON133','Active','43904','PT110'
'12','ABC','123','1','38615','CON134','Active','43904','PT110'
'13','ABC','123','1','38611','CON135','Active','43904','PT110'
'14','ABC','123','1','38612','CON136','Active','43904','PT110'
'15','ABC','123','2','38627','CON137','Active','43904','PT110'
'16','ABC','123','1','38610','CON138','Active','43904','PT110'
'17','ABC','123','2','38630','CON139','Active','43904','PT110'
'18','ABC','123','2','38628','CON140','Active','43904','PT110'
'19','ABC','123','2','38632','CON141','Active','43904','PT110'
'20','ABC','123','2','38621','CON142','Active','43904','PT110'
'21','ABC','123','1','60631','CON143','Active','43904','PT110'
'22','ABC','123','2','38631','CON144','Active','43904','PT110'
'23','ABC','123','1','38613','CON145','Active','43904','PT110'
'24','ABC','123','1','60626','CON146','Active','43904','PT110'
'25','ABC','123','1','60630','CON147','Active','43904','PT110'
'26','ABC','123','2','38625','CON148','Active','43904','PT110'
'27','ABC','123','2','60628','CON149','Active','43904','PT110'
'28','ABC','123','2','38624','CON150','Active','43904','PT110'
'29','ABC','123','2','38623','CON151','Active','43904','PT110'
'30','ABC','123','2','38620','CON152','Active','43904','PT110'

SELECT '1','ABC','123','1','38619','CON123','Active','43904','PT110' UNION
SELECT '2','ABC','123','2','38629','CON124','Active','43904','PT110' UNION
SELECT '3','ABC','123','2','38618','CON125','Active','43904','PT110' UNION
SELECT '4','ABC','123','1','38614','CON126','Active','43904','PT110' UNION
SELECT '5','ABC','123','1','60629','CON127','Active','43904','PT110' UNION
SELECT '6','ABC','123','1','38609','CON128','Active','43904','PT110' UNION
SELECT '7','ABC','123','2','38622','CON129','Active','43904','PT110' UNION
SELECT '8','ABC','123','2','38616','CON130','Active','43904','PT110' UNION
SELECT '9','ABC','123','2','60627','CON131','Active','43904','PT110' UNION
SELECT '10','ABC','123','2','38617','CON132','Active','43904','PT110' UNION
SELECT '11','ABC','123','2','38626','CON133','Active','43904','PT110' UNION
SELECT '12','ABC','123','1','38615','CON134','Active','43904','PT110' UNION
SELECT '13','ABC','123','1','38611','CON135','Active','43904','PT110' UNION
SELECT '14','ABC','123','1','38612','CON136','Active','43904','PT110' UNION
SELECT '15','ABC','123','2','38627','CON137','Active','43904','PT110' UNION
SELECT '16','ABC','123','1','38610','CON138','Active','43904','PT110' UNION
SELECT '17','ABC','123','2','38630','CON139','Active','43904','PT110' UNION
SELECT '18','ABC','123','2','38628','CON140','Active','43904','PT110' UNION
SELECT '19','ABC','123','2','38632','CON141','Active','43904','PT110' UNION
SELECT '20','ABC','123','2','38621','CON142','Active','43904','PT110' UNION
SELECT '21','ABC','123','1','60631','CON143','Active','43904','PT110' UNION
SELECT '22','ABC','123','2','38631','CON144','Active','43904','PT110' UNION
SELECT '23','ABC','123','1','38613','CON145','Active','43904','PT110' UNION
SELECT '24','ABC','123','1','60626','CON146','Active','43904','PT110' UNION
SELECT '25','ABC','123','1','60630','CON147','Active','43904','PT110' UNION
SELECT '26','ABC','123','2','38625','CON148','Active','43904','PT110' UNION
SELECT '27','ABC','123','2','60628','CON149','Active','43904','PT110' UNION
SELECT '28','ABC','123','2','38624','CON150','Active','43904','PT110' UNION
SELECT '29','ABC','123','2','38623','CON151','Active','43904','PT110' UNION
SELECT '30','ABC','123','2','38620','CON152','Active','43904','PT110'
;

still getting syntax error: expected something between the "int" and "insert"

expect something between ")" and "insert".

create table #cust_tbl(item int, current_cust_id char(3), major_acct char(3), minor_acct char(3), cust_order_no char(5), cust_acct_status char(8), revision_dt date, part_type char(10), qty_cust_order int)
insert into #cust_tbl

error
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into #cust_tbl
SELECT '1','ABC','123','1','38619','CON123','Active','4390' at line 2

so on sql fiddle you have to make sure to select MS SQL SERVER 2017 from the drop down on top left

image

#Cust_Tbl(item int, CURRENT_CUST char(3), MAJOR_MODEL char(3), MINOR_MODEL int, MODEL_SERIAL_NBR int, MODEL_NBR int, CUST_DELIVERY_STATUS char(10), PART_NBR char(20), MODEL_QTY int)
insert into #Cust_Tbl
INSERT 1,'ABC','M3','8','38619','ZA453','Active','1006927-110',197,UNION
INSERT 1,'ABC','M3','9','38629','ZB374','Active','1006927-110',38,UNION
INSERT 1,'ABC','M3','9','38618','ZB365','Active','1006927-110',38,UNION
INSERT 1,'ABC','M3','8','38614','ZA456','Active','1006927-110',197,UNION
INSERT 1,'ABC','M3','8','60629','ZA459','Active','1006927-110',197,UNION
INSERT 1,'ABC','M3','8','38609','ZA450','Active','1006927-110',197,UNION
INSERT 1,'ABC','M3','9','38622','ZB367','Active','1006927-110',38,UNION
INSERT 1,'ABC','M3','9','38616','ZB362','Active','1006927-110',38,UNION
INSERT 1,'ABC','M3','9','60627','ZB378','Active','1006927-110',38,UNION
INSERT 1,'ABC','M3','9','38617','ZB363','Active','1006927-110',38,UNION
INSERT 1,'ABC','M3','9','38626','ZB371','Active','1006927-110',38,UNION
INSERT 1,'ABC','M3','8','38615','ZA457','Active','1006927-110',197,UNION
INSERT 1,'ABC','M3','8','38611','ZA452','Active','1006927-110',197,UNION
INSERT 1,'ABC','M3','8','38612','ZA454','Active','1006927-110',197,UNION
INSERT 1,'ABC','M3','9','38627','ZB372','Active','1006927-110',38,UNION
INSERT 1,'ABC','M3','8','38610','ZA451','Active','1006927-110',137,UNION
INSERT 1,'ABC','M3','9','38630','ZB375','Active','1006927-110',38,UNION
INSERT 1,'ABC','M3','9','38628','ZB373','Active','1006927-110',38,UNION
INSERT 1,'ABC','M3','9','38632','ZB377','Active','1006927-110',38,UNION
INSERT 1,'ABC','M3','9','38621','ZB364','Active','1006927-110',38,UNION
INSERT 1,'ABC','M3','8','60631','ZA950','Active','1006927-110',197,UNION
INSERT 1,'ABC','M3','9','38631','ZB376','Active','1006927-110',38,UNION
INSERT 1,'ABC','M3','8','38613','ZA455','Active','1006927-110',197,UNION
INSERT 1,'ABC','M3','8','60626','ZA458','Active','1006927-110',197,UNION
INSERT 1,'ABC','M3','8','60630','ZA949','Active','1006927-110',197,UNION
INSERT 1,'ABC','M3','9','38625','ZB370','Active','1006927-110',38,UNION
INSERT 1,'ABC','M3','9','60628','ZB379','Active','1006927-110',38,UNION
INSERT 1,'ABC','M3','9','38624','ZB369','Active','1006927-110',38,UNION
INSERT 1,'ABC','M3','9','38623','ZB368','Active','1006927-110',38,UNION
INSERT 1,'ABC','M3','9','38620','ZB366','Active','1006927-110',38
;

newbie creating table...sql fiddle says syntax error around 'item' I was locked out yesterday due to maximum post allowed. sorry.

got rid of all "int" and use "char", and got rid of the "," in front of UNION

#Cust_Tbl(item int, CURRENT_CUST char(3), MAJOR_MODEL char(3), MINOR_MODEL char(5), MODEL_SERIAL_NBR char(5), MODEL_NBR char(8), CUST_DELIVERY_STATUS char(10), PART_NBR char(20), MODEL_QTY char(3))
insert into #Cust_Tbl
INSERT 1,'ABC','M3','8','38619','ZA453','Active','1006927-110',197 UNION
INSERT 1,'ABC','M3','9','38629','ZB374','Active','1006927-110',38 UNION
INSERT 1,'ABC','M3','9','38618','ZB365','Active','1006927-110',38 UNION
INSERT 1,'ABC','M3','8','38614','ZA456','Active','1006927-110',197 UNION
INSERT 1,'ABC','M3','8','60629','ZA459','Active','1006927-110',197 UNION
INSERT 1,'ABC','M3','8','38609','ZA450','Active','1006927-110',197 UNION
INSERT 1,'ABC','M3','9','38622','ZB367','Active','1006927-110',38 UNION
INSERT 1,'ABC','M3','9','38616','ZB362','Active','1006927-110',38 UNION
INSERT 1,'ABC','M3','9','60627','ZB378','Active','1006927-110',38 UNION
INSERT 1,'ABC','M3','9','38617','ZB363','Active','1006927-110',38 UNION
INSERT 1,'ABC','M3','9','38626','ZB371','Active','1006927-110',38 UNION
INSERT 1,'ABC','M3','8','38615','ZA457','Active','1006927-110',197 UNION
INSERT 1,'ABC','M3','8','38611','ZA452','Active','1006927-110',197 UNION
INSERT 1,'ABC','M3','8','38612','ZA454','Active','1006927-110',197 UNION
INSERT 1,'ABC','M3','9','38627','ZB372','Active','1006927-110',38 UNION
INSERT 1,'ABC','M3','8','38610','ZA451','Active','1006927-110',137 UNION
INSERT 1,'ABC','M3','9','38630','ZB375','Active','1006927-110',38 UNION
INSERT 1,'ABC','M3','9','38628','ZB373','Active','1006927-110',38 UNION
INSERT 1,'ABC','M3','9','38632','ZB377','Active','1006927-110',38 UNION
INSERT 1,'ABC','M3','9','38621','ZB364','Active','1006927-110',38 UNION
INSERT 1,'ABC','M3','8','60631','ZA950','Active','1006927-110',197 UNION
INSERT 1,'ABC','M3','9','38631','ZB376','Active','1006927-110',38 UNION
INSERT 1,'ABC','M3','8','38613','ZA455','Active','1006927-110',197 UNION
INSERT 1,'ABC','M3','8','60626','ZA458','Active','1006927-110',197 UNION
INSERT 1,'ABC','M3','8','60630','ZA949','Active','1006927-110',197 UNION
INSERT 1,'ABC','M3','9','38625','ZB370','Active','1006927-110',38 UNION
INSERT 1,'ABC','M3','9','60628','ZB379','Active','1006927-110',38 UNION
INSERT 1,'ABC','M3','9','38624','ZB369','Active','1006927-110',38 UNION
INSERT 1,'ABC','M3','9','38623','ZB368','Active','1006927-110',38 UNION
INSERT 1,'ABC','M3','9','38620','ZB366','Active','1006927-110',38
;

Worked, "select" :frowning: