# Find Top x per month

• ### Question

• Thanks for taking the time to read my question.

I have a table tblData with UserName, InvoiceDate and TotalData

There are several Invoice dates per month, so I need to convert InvoiceDate to YearMonth to equalize the data

I am trying to figure out how to return the top 10 highest data users per month, but all I can seem to get is the top 10 users overall...

I have looked on line a lot but am having trouble understanding what the posts say vs. what I'm working with. I think I have to do a sub query but not really sure how to apply that to my query so that I get to 10 for all my YearMonth's and not just the top 10 overall.

any help would be great.

Thanks,

Monday, February 6, 2017 9:18 PM

• Try this:

YEAR(InvoiceDate) AS InvoiceYear,
MONTH(InvoiceDate) AS InvoiceMonth,
SUM(TotalData) AS DataUsed
FROM tblData AS T1
FROM tblData AS T2
WHERE YEAR(T2.InvoiceDate) = YEAR(T1.InvoiceDate)
AND MONTH(T2.InvoiceDate) =MONTH(T1.InvoiceDate)
ORDER BY SUM(TotalData) DESC)
ORDER BY YEAR(InvoiceDate), MONTH(InvoiceDate),SUM(TotalData) DESC;

Ken Sheridan, Stafford, England

• Marked as answer by Tuesday, February 7, 2017 1:36 PM
Tuesday, February 7, 2017 1:06 PM

### All replies

• SELECT TOP 10 FROM tblDATA Group By Month(InvoiceDate);

See if that does the trick.

Monday, February 6, 2017 9:21 PM
• Hi Curt,

thanks for your quick reply. That returns me the top 10 users with the most recent InvoiceDate. I'd like the top 10 users with the highest data value per Month.

Monday, February 6, 2017 9:22 PM
• SELECT Format(InvoiceDate,"yyyy-mm") AS Expr1, tblData.UserName, tblData.TotalData
FROM tblDATA
WHERE ((((select count(asterist here) from tblData as f
WHERE Format(f.InvoiceDate,yyyy-mm) = Format(tblData.InvoiceDate,yyyy-mm) and f.TotalData <= tblData.TotalData
))>=10)) ORDER BY Format(InvoiceDate,yyyy-mm);

It won't let me put an asterisk, so I've used a placeholder.

Monday, February 6, 2017 9:50 PM
• Try this:

YEAR(InvoiceDate) AS InvoiceYear,
MONTH(InvoiceDate) AS InvoiceMonth,
SUM(TotalData) AS DataUsed
FROM tblData AS T1
FROM tblData AS T2
WHERE YEAR(T2.InvoiceDate) = YEAR(T1.InvoiceDate)
AND MONTH(T2.InvoiceDate) =MONTH(T1.InvoiceDate)
ORDER BY SUM(TotalData) DESC)
ORDER BY YEAR(InvoiceDate), MONTH(InvoiceDate),SUM(TotalData) DESC;

Ken Sheridan, Stafford, England

• Marked as answer by Tuesday, February 7, 2017 1:36 PM
Tuesday, February 7, 2017 1:06 PM
• Hi Ken,

Thank you so VERY much for that query. That did EXACTLY what I needed!!! I was so close but you used the fields in a different way than I did, which was the difference.

have a great day.