help with Top 20 SKUS

[Ira]

Hi Guys,



Working on a query to give me the Top 20 SKUs.

I got this to work: ProductCode is SKU

SELECT TOP 20 Count(OrderDetails.ProductCode) AS CountOfProductCode, OrderDetails.ProductCode, OrderDetails.Packaging
FROM Orders LEFT JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
WHERE (((Orders.ShipDate) Between [Forms]![dlgDate]![StartDate] And [Forms]![dlgDate]![EndDate]) AND ((Orders.Rep)="Amazon"))
GROUP BY OrderDetails.ProductCode, OrderDetails.Packaging
ORDER BY Count(OrderDetails.ProductCode) DESC;

But I need to add one more field to get the correct count. That is Quantity. I need apply the quality to Number of ProductCode (SKUs). Not sure how to account for customers buying 2 and 3 or more of the same SKU.

Thank you,

Ira

[ranman256]

make Q1 to count the SKU's,
then Q2 will use Q1 to get the top 20

[CJ_London]

Is that quantity or quality?

assuming the former why can’t you just sum it?

[Ira]

Sorry, Quantity. I tired this, but getting errors.

SELECT TOP 20 (Count(OrderDetails.ProductCode)*OrderDetails.QTY) AS CountOfProductCode, OrderDetails.ProductCode, OrderDetails.Packaging, OrderDetails.QTY
FROM Orders LEFT JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
WHERE (((Orders.ShipDate) Between [Forms]![dlgDate]![StartDate] And [Forms]![dlgDate]![EndDate]) AND ((Orders.Rep)="Amazon"))
GROUP BY OrderDetails.ProductCode, OrderDetails.Packaging
ORDER BY (Count(OrderDetails.ProductCode)*OrderDetails.QTY) DESC;

[orange]

See this Allen Browne article on Subquery and Top n for info

[CJ_London]

what sort of errors?

I can see your query doesn't make sense - why try to count the product (one value) and multiply by qty (multiple values)?

e,g, say your data was

Product Qty
1.........10
1..........5
1..........20
2..........7
2..........9

so count of product would be

1....3
2....2

which you then want to multiply by qty so you get

1.....30
1......15
1......60
2......14
2......18

your query also needs to group by qty which you are not doing
why not just

SELECT TOP 20 Count(OrderDetails.ProductCode) as codeCount, sum(OrderDetails.QTY) as ttlqty, ........

[Ira]

Okay, so maybe I am not making myself clear

I have orders with details like:


ProductCode QTY

AAA 10
BBB 2
CCC 1
CCC 1
CCC 1
CCC 1
BBB 2
BBB 2

Write now BBB and CCC comes up as the TOP 2, but In reality AAA is the TOP1. How do I take into account the QTY?

[CJ_London]

so you want top based on sum orders, not count of orders? in which case you need to order by the sum, before the count

[Ira]

Still can't get it.

Tried this, but I keep getting prompted for SumOfProductCode

\SELECT TOP 20 Sum(OrderDetails.QTY) AS SumOfProductCode, OrderDetails.ProductCode, OrderDetails.PackagingFROM Orders LEFT JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
WHERE (((Orders.ShipDate) Between #8/29/2022# And #9/1/2022#) AND ((Orders.Rep)="Amazon"))
GROUP BY SumOfProductCode, OrderDetails.ProductCode, OrderDetails.Packaging
ORDER BY SumOfProductCode DESC;

[CJ_London]

suggest use the query builder to build your query so you can see how it is constructed

[NoellaG]

Normally T-SQL first executes the FROM clause, then the WHERE, then the GROUP BY, then the SELECT and last the ORDR BY (could be slightly different in the Access dialect of SQL). The named field SumOfProductCode is created in the SELECT statement, so doesn't exist yet when the GROUP BY is executed. Hence the error message.
So leave the SumOfProductCode field out of the GROUP by expression. You can use it in the ORDER BY clause.

[Micron]

A google search didn't provide me with specific info about the order of ms access query operations but I did find this vid which also contains a link to this article either of which might provide a tool that answers that puzzle if no one chimes in with the details. In any event, I imagine the feature could be quite useful to people who don't know about it. I decided not to make use of it as I'm no longer involved in mining large db's.