cancel
Showing results for 
Search instead for 
Did you mean: 

Top n Items per Industry Group

Former Member
0 Kudos
49

I'm working on a query that will pull the TOP(20) Items sold for each Industry for a period of time. This is the query that I've started with as a base which pulls all Items, grouped by Industry and then the total qty sold in desc order.


SELECT T0.[U_Industry], T2.[ItemCode], SUM(T2.[Quantity]) as QTY 
FROM OCRD T0  
INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode 
INNER JOIN INV1 T2 ON T1.DocEntry = T2.DocEntry 
WHERE T1.[DocDate] >=[%0] and  T1.[DocDate] <=[%1] 
GROUP BY T0.[U_Industry], T2.[ItemCode] 
ORDER BY T0.[U_Industry], SUM(T2.[Quantity]) DESC

It seems that there are multiple ways to do this (RANK, ROW_NUMBERS,etc.), but I'm having a hard time getting it to work in SAPB1. Any help would be appreciated. Thanks,

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi,

You may add Rank() to this query as a condition:


SELECT Top 2000 T0.[U_Industry],T1.[ItemCode], SUM(T1.[Quantity]) as QTY 
FROM OINV T0
INNER JOIN INV1 T1 ON T1.DocEntry = T0.DocEntry 
WHERE T0.[DocDate] >=[%0] and  T0.[DocDate] <=[%1] 
GROUP BY T0.[U_Industry], T1.[ItemCode]
HAVING SUM(T1.[Quantity]) > 0
ORDER BY T0.[U_Industry], SUM(T1.[Quantity]) DESC, T1.[ItemCode]

Thanks,

Gordon

Former Member
0 Kudos

Gordon, do you know if SAPB1 supports PARTITION? This is where I'm at and receiving "Incorrect syntax near the keyword 'DESC' ".


With TMP as
( 
	SELECT T0.[U_Industry], T2.[ItemCode], SUM(T2.[Quantity]) as QTY,
	RANK() OVER (PARTITION by T0.[U_Industry] ORDER BY SUM(T2.[Quantity])DESC) as TMP_RANK
	FROM OCRD T0  
	INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode 
	INNER JOIN INV1 T2 ON T1.DocEntry = T2.DocEntry 
	WHERE T1.[DocDate] >=[%0] and  T1.[DocDate] <=[%1] 
	GROUP BY T0.[U_Industry], T2.[ItemCode]
)
SELECT *
FROM TMP
WHERE TMP.TMP_RANK <= 5

Former Member
0 Kudos

I have never tried. What is your SQL Server version?

Former Member
0 Kudos

2005

Former Member
0 Kudos

I was able to successfully run the query through the Excel ODBC Microsoft Query to get the data, so I my question is answered on whether SAPB1 supports PARTITION. Thanks for the response.

Former Member
0 Kudos

Hi Karl.....

Try this......

SELECT Top 20 T2.[ItemCode], T0.[U_Industry], SUM(T2.[Quantity]) as QTY 
FROM OCRD T0  
INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode 
INNER JOIN INV1 T2 ON T1.DocEntry = T2.DocEntry 
WHERE T1.[DocDate] >=[%0] and  T1.[DocDate] <=[%1] 
GROUP BY T0.[U_Industry], T2.[ItemCode] 
ORDER BY T0.[U_Industry], SUM(T2.[Quantity]) DESC

Regards,

Rahul