Top 3 products for each store

[Donald]

Hello.

I am trying to pull the top 3 products (highest $ Ending Inventory value) for each of our stores.

I am working with table "Weekly Ending Inventory"

Field's I am using are 'Short Name', 'Product_Name', and 'Total Ending Inventory'

Here is what I would like my results to look like

Short Name Product Name Total Ending Inventory
Boston Chicken Strips $350
Boston Charp Cheddar $105
Boston Lettuce $20
Los Angeles Beef Patty $425
Los Angeles Chicken Breast $408
Los Angeles French Fries $355

I have looked at similar post, but have been unsuccessful.

Please help.

 

[jackd]

Hello.

I am trying to pull the top 3 products (highest $ Ending Inventory value) for each of our stores.

I am working with table "Weekly Ending Inventory"

Field's I am using are 'Short Name', 'Product_Name', and 'Total Ending Inventory'

Here is what I would like my results to look like

Short Name Product Name Total Ending Inventory
Boston Chicken Strips $350
Boston Charp Cheddar $105
Boston Lettuce $20
Los Angeles Beef Patty $425
Los Angeles Chicken Breast $408
Los Angeles French Fries $355

I have looked at similar post, but have been unsuccessful.

Please help.

Donald,

I provided the following query for someone trying to do a TOP 2 by group recently. It doesn't match your fields/tables but it may provide some help.

SELECT T1.EmployeeId, T1.EmployeeName, T1.EmployeeLocation, T1.MyDate, T1.Amount, T1.NewId
FROM LOCATIONS AS T1
WHERE (((T1.NewId) In (SELECT TOP 2 T2.NewID
FROM LOCATIONS As T2
WHERE T2.EmployeeID = T1.EmployeeID
ORDER BY T2.NewID DESC)))
ORDER BY T1.EmployeeName;

Please post your table layout etc if you need further assistance.

Good luck

 

[Donald]

SELECT [Weekly Ending Inventory].[Short Name], [Weekly Ending Inventory].GL, [Weekly Ending Inventory].PRODUCT_NAME, Sum([Weekly Ending Inventory].[Total Ending Inventory]) AS [SumOfTotal Ending Inventory]
FROM [Weekly Ending Inventory]
GROUP BY [Weekly Ending Inventory].[Short Name], [Weekly Ending Inventory].GL, [Weekly Ending Inventory].PRODUCT_NAME
HAVING ((([Weekly Ending Inventory].GL)="Food"))
ORDER BY Sum([Weekly Ending Inventory].[Total Ending Inventory]) DESC;


Please let me know if this is what you need.

Thanks for the help.

 

[jackd]

SELECT [Weekly Ending Inventory].[Short Name], [Weekly Ending Inventory].GL, [Weekly Ending Inventory].PRODUCT_NAME, Sum([Weekly Ending Inventory].[Total Ending Inventory]) AS [SumOfTotal Ending Inventory]
FROM [Weekly Ending Inventory]
GROUP BY [Weekly Ending Inventory].[Short Name], [Weekly Ending Inventory].GL, [Weekly Ending Inventory].PRODUCT_NAME
HAVING ((([Weekly Ending Inventory].GL)="Food"))
ORDER BY Sum([Weekly Ending Inventory].[Total Ending Inventory]) DESC;


Please let me know if this is what you need.

Thanks for the help.

Donald,

My intent was to show you a pattern for the SQL that might be suited to your situation. It worked identifying the TOP 2 by Location. I'll try to mock up some additional data based on your initial post to see if I can get something to work.

How does your SQL identify the TOP 3?
Do you have some additional data from your [Weekly Ending Inventory]?

 

[jackd]

Donald,

My intent was to show you a pattern for the SQL that might be suited to your situation. It worked identifying the TOP 2 by Location. I'll try to mock up some additional data based on your initial post to see if I can get something to work.

How does your SQL identify the TOP 3?
Do you have some additional data from your [Weekly Ending Inventory]?

I have done some tinkering.

I created a table WeeklyEndingInventory:

Fields

field_name
shortname
GL
ProductName
totalEndingInventory

and added some data for testing

shortname	GL	ProductName	totalEndingInventory
Boston	Food	Chicken Strips	350
Boston	Food	Charp Cheddar	105
Boston	Food	Lettuce	20
Los Angeles	Food	Beef Patty	425
Los Angeles	Food	Chicken Breast	408
Los Angeles	Food	French Fries	355
Boston	Food	Taco Salad	45
Los Angeles	Food	Raw Squid	381
Boston	Food	Pepsi	34
Boston	Food	Broccoli	210
Boston	Food	Cheese Sticks	70
Los Angeles	Food	Lettuce	401

Then created a new table WeeklyEndingInventory_new from the existing
WeeklyEndingInventory table but sorted by totalEndingInventory DESCENDING

 SELECT WeeklyEndingInventory.shortname
, WeeklyEndingInventory.GL, WeeklyEndingInventory.ProductName
, WeeklyEndingInventory.totalEndingInventory INTO WeeklyEndingInventory_new
FROM WeeklyEndingInventory
ORDER BY WeeklyEndingInventory.totalEndingInventory DESC;

Then the key to selecting the top 3, is to add a new field (in my case JId) to the new table, make the field autonumber so that it adds a new number to each of the sorted records.

THen the query to select the top3 totalEndingInventory by productname by City

 SELECT t1.shortname, t1.ProductName, t1.totalEndingInventory
FROM WeeklyEndingInventory_New AS t1
WHERE (((t1.Jid) In (SELECT TOP 3 T2.JId FROM WeeklyEndingInventory_new As T2
 WHERE T2.shortname = T1.shortname ORDER BY T2.JID asC)))
ORDER BY t1.shortname, t1.totalEndingInventory DESC;

Giving Top 3 by city

shortname	ProductName	totalEndingInventory
Boston	Chicken Strips	$350.00
Boston	Broccoli	$210.00
Boston	Charp Cheddar	$105.00
Los Angeles	Beef Patty	$425.00
Los Angeles	Chicken Breast	$408.00
Los Angeles	Lettuce	$401.00

 

[Donald]

Awesome.. Thanks. It works fine.

Can you help a little further.

In this example, I am pulling top 3 total ending inventory by productname for each city for GL Food.

What I would like to do now is still pull the top 3 products for each city by GL food, but also GL Beer.

Below is what I would like my results to look like.


ShortName GL Product Name TotalEndingInventory
Boston Food Chicken Strips $350.00
Boston Food Broccoli $210.00
Boston Food Charp Cheddar $105.00
Boston Beer Bud Light $406.00
Boston Beer Sam Adams $298.00
Boston Beer Budweiser $246.00



Thanks Jack for your help!!!