Top 10 Items By Dept

Question

I am having issues in adjusting this query to grab the top 10 selling styles by dept. I have seen various examples around the web on how to do this with a sub query but none of them have conditions or joins included so I am a bit lost.

My current query is below. Any help would be appreciated!

SELECT
    T1.DEPT,
    T1.SKU,
    T1.COLOR,
    SUM(T1.SALES) AS Sales,
    SUM(T1.GP) AS GP
FROM Table1 T1
    INNER JOIN Table2 T2 ON T1.Date = T2.Date
WHERE
    T2.Year = '2017'
    AND T2.Month in ("July, August")
GROUP BY
    T1.DEPT, 
    T1.SKU, 
    T1.COLOR
ORDER BY 4

Similar to: https://stackoverflow.com/questions/41220690/top-n-records-per-group-sql-in-access using correlated subquery and in — xQbert, Oct 19 '17 at 19:39
SELECT TOP 10.... https://stackoverflow.com/questions/9339079/how-to-select-top-10-in-access-query — Frank, Oct 19 '17 at 20:00
@xQbert I've tried this example but I am still stuck as my example uses sums and groupings and I am unsure how to apply that to the parent and subquery in this case. — clhalen, Oct 19 '17 at 20:00
@Frank This only selects the top 10 queries, not the top 10 by Dept. — clhalen, Oct 19 '17 at 20:02
Are you looking for top 10 in each dept or top 10 on the whole. The one which has the highest sum(sales) is the top selling style? — Valli, Oct 19 '17 at 20:18

score 0 · Answer 1 · answered Oct 19 '17 at 20:19

0

SELECT TOP 10 T1.DEPT, T1.SKU, T1.COLOR FROM T1 IN (
SELECT
    T1.DEPT,
    T1.SKU,
    T1.COLOR,
    SUM(T1.SALES) AS Sales,
    SUM(T1.GP) AS GP
FROM Table1 T1
    INNER JOIN Table2 T2 ON T1.Date = T2.Date
WHERE
    T2.Year = '2017'
    AND T2.Month in ("July, August")
GROUP BY
    T1.DEPT, 
    T1.SKU, 
    T1.COLOR
ORDER BY 4)

answered Oct 19 '17 at 20:19

Frank

31
8

OP requires *by department* – Parfait Oct 19 '17 at 20:19
you may have to run a query separate for each department to get what you're asking for. – Frank Oct 19 '17 at 20:21

score 0 · Answer 2 · answered Oct 19 '17 at 20:33

Simply save your aggregate query as a stored Access query and then run a correlated count subquery in a new query referencing the aggregate:

SELECT DEPT, SKU, COLOR, Sales, GP,
      (SELECT Count(*) FROM QueryAgg sub
       WHERE sub.Sales >= QueryAgg.Sales
         AND sub.DEPT = QueryAgg.DEPT) As [Rank]
FROM QueryAgg
WHERE (SELECT Count(*) FROM QueryAgg sub
       WHERE sub.Sales >= QueryAgg.Sales
         AND sub.DEPT = QueryAgg.DEPT) <= 10
ORDER BY DEPT, Sales DESC

score 0 · Answer 3 · answered Oct 19 '17 at 21:02

select T.dept,
       T.sku,
       T.color,
       T.Sales as sumsales,
       T.GP as sumgp
       row_number() over(partition by T.dept order by T.Sales desc) as rownum
from (SELECT T1.DEPT, T1.SKU, T1.COLOR,
             SUM(T1.SALES) AS Sales,
             SUM(T1.GP) AS GP
        FROM Table1 T1
        INNER JOIN Table2 T2 ON T1.Date = T2.Date
         WHERE T2.Year = '2017'
          AND T2.Month in ("July, August")
      GROUP BY T1.DEPT, T1.SKU, T1.COLOR) T
where T.rownum <= 10

Taking your query in From Clause. Then splitting based on departments, ordering by sum of sales. Then retrieving the top 10 sum of sales for each department

You can replace this

FROM Table1 T1
        INNER JOIN Table2 T2 ON T1.Date = T2.Date
         WHERE T2.Year = '2017'
          AND T2.Month in ("July, August")

AS

FROM TABLE T1
WHERE T1.Year = '2017'
  AND T1.Month in ("July", "August")

I dont see T2 used anywhere and there iw no need of self join. It can be handled with the where clause

Top 10 Items By Dept

3 Answers3