Selecting Top 3 Items from a Category and Subcategory

  • Thread starter Manuel Indacochea via AccessMonster.com
  • Start date
M

Manuel Indacochea via AccessMonster.com

Having the table named [Top3Sales] with some date like that:

Dept Item Sales
1223 OPQ $868.00
1229 OPQ $1,956.00
1229 RST $353.00
1229 LMN $8,232.00
1229 ABC $4,482.00
1356 OPQ $3,189.00
1356 ABC $9,386.00
1356 HJK $2,542.00
1360 OPQ $9,439.00
1363 OPQ $114.00
1363 RST $7,225.00
1374 OPQ $27.00
1374 RST $8,607.00
1374 LMN $8,087.00
1374 HJK $464.00

I would like to have a query that shows the Top 3 item with higher amount
of sells, grouped by department, something like the following:

Dept Item Total
1223 OPQ $868
1229 ABC $4,482
1229 LMN $8,232
1229 OPQ $1,956
1356 ABC $9,386
1356 HJK $2,542
1356 OPQ $3,189
1360 OPQ $9,439
1363 OPQ $114
1363 RST $7,225
1374 HJK $464
1374 LMN $8,087
1374 OPQ $27

Notice that there may be departments with only 1 or 2 items sold.

I already reviewed previous messages posted and found nothing similar to
this, so I would appreciate so much any assistance.
 
J

JR

I tried this using the data you posted.

SELECT TOP 3 tblSales.Dept, tblSales.Item, Sum(tblSales.Sales) AS [Total
Sales]
FROM tblSales
GROUP BY tblSales.Dept, tblSales.Item
ORDER BY Sum(tblSales.Sales) DESC

Results

Dept Item Total Sales
1360 OPQ $9,439.00
1356 ABC $9,386.00
1374 RST $8,607.00

Perhaps this will help?


JR
 
M

Manuel Indacochea via AccessMonster.com

Thanks for your time, but unfortunatelly this is not what I need. I want to
get the Top 3 "Most Sold" Items in each plant. From the data I provided on
original posting, I need the first table analyzed and then obtain the
second one. In other words, what I'm looking for is:

Dept Item Total
1223 OPQ $868

1229 LMN $8,232
1229 ABC $4,482
1229 OPQ $1,956

1356 ABC $9,386
1356 OPQ $3,189
1356 HJK $2,542

Please advise if you can help.
1360 OPQ $9,439
 
J

John Spencer (MVP)

You will need a coordinated subquery to do this. UNTESTED SQL Statement follows.

SELECT Dept, Item, Total
FROM Top3Sales as S
WHERE Item in
(SELECT TOP 3 T.Item
FROM Top3Sales as T
WHERE T.Dept = S.Dept
ORDER BY T.Sales DESC)

Note that in case of ties you could end up with more than 3 rows for any Dept code.
 
M

Manuel Indacochea via AccessMonster.com

This is excellent buddy. Ihis worked fine. Thanks so much for your help.
 
X

XMan

This is brilliant! Thanks for this tip!


John Spencer (MVP) said:
You will need a coordinated subquery to do this. UNTESTED SQL Statement follows.

SELECT Dept, Item, Total
FROM Top3Sales as S
WHERE Item in
(SELECT TOP 3 T.Item
FROM Top3Sales as T
WHERE T.Dept = S.Dept
ORDER BY T.Sales DESC)

Note that in case of ties you could end up with more than 3 rows for any Dept code.

Manuel Indacochea via AccessMonster.com said:
Having the table named [Top3Sales] with some date like that:

Dept Item Sales
1223 OPQ $868.00
1229 OPQ $1,956.00
1229 RST $353.00
1229 LMN $8,232.00
1229 ABC $4,482.00
1356 OPQ $3,189.00
1356 ABC $9,386.00
1356 HJK $2,542.00
1360 OPQ $9,439.00
1363 OPQ $114.00
1363 RST $7,225.00
1374 OPQ $27.00
1374 RST $8,607.00
1374 LMN $8,087.00
1374 HJK $464.00

I would like to have a query that shows the Top 3 item with higher amount
of sells, grouped by department, something like the following:

Dept Item Total
1223 OPQ $868
1229 ABC $4,482
1229 LMN $8,232
1229 OPQ $1,956
1356 ABC $9,386
1356 HJK $2,542
1356 OPQ $3,189
1360 OPQ $9,439
1363 OPQ $114
1363 RST $7,225
1374 HJK $464
1374 LMN $8,087
1374 OPQ $27

Notice that there may be departments with only 1 or 2 items sold.

I already reviewed previous messages posted and found nothing similar to
this, so I would appreciate so much any assistance.
Click to expand...
Click to expand...
 
Top