Top 3 products for each store

Donald

Board Regular
Joined
Apr 15, 2002
Messages
138
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.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,308
Office Version
  1. 365
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.

Code:
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

Board Regular
Joined
Apr 15, 2002
Messages
138
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

Well-known Member
Joined
Oct 19, 2006
Messages
1,308
Office Version
  1. 365
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

Well-known Member
Joined
Oct 19, 2006
Messages
1,308
Office Version
  1. 365
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
Code:
field_name
shortname
GL
ProductName
totalEndingInventory
and added some data for testing
Code:
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

Code:
 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

Code:
 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
Code:
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

Board Regular
Joined
Apr 15, 2002
Messages
138
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!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,159
Messages
5,623,092
Members
415,951
Latest member
Shen1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top