I am creating a Database for my utility bills in SSMS18, and I am trying to retrieve the count for the occurrence of a specific month in the top 10 months with the highest bills. How do I restrict the COUNT feature to only count the top number of rows?

Table design

CREATE TABLE Electric (
                        [date] date NOT NULL, 
                        electric_bill_amount smallmoney);

I can limit the results by adding a WHERE statement

SELECT DISTINCT          DATENAME(MONTH, [date]) AS MONTH,
                         COUNT(DATENAME(MONTH, [date])) AS [Frequency]
FROM Electric
WHERE electric_bill_amount > 104
GROUP BY DATENAME(MONTH, [date])
ORDER BY [Frequency] DESC

but I would like to my query to be more dynamic and therefore only have the count statement use the months with the top 10 highest values.

Results should look something like this

August 3
September 3
July 2
January 1
December 1

CodePudding user response：

1. I guess you are using SQL Server, not MySQL.
2. Just to clarify that you want to get the top 10 highest electric bill amount and see if each of these falls on which month.
3. Tested on dbfiddle

SELECT         DATENAME(MONTH, [date]) AS MONTH,
               COUNT(DATENAME(MONTH, [date])) AS [Frequency]
FROM (
    SELECT [date], electric_bill_amount, RANK() OVER(ORDER BY electric_bill_amount DESC) as r
    FROM Electric
) tmp
WHERE r <= 10
GROUP BY DATENAME(MONTH, [date])
ORDER BY [Frequency] DESC;