get average per month in a SQL query-CodePudding

I have the following query:

SELECT DISTINCT 
    DATENAME(month, CurrentDate) AS MonthName, 
    DATENAME(year, CurrentDate) AS Year, 
    MONTH(CurrentDate) AS MonthNumber
FROM
    dbo.Prices
GROUP BY 
    CurrentDate, DATENAME(month, CurrentDate), 
    DATENAME(year, CurrentDate), MONTH(CurrentDate)

When I run this I get one row as result which is expected

The table has two columns CurrentDate and CurrentPrice, but when I run this query...

SELECT        
    DATENAME(month, CurrentDate) AS MonthName, 
    DATENAME(year, CurrentDate) AS Year, 
    MONTH(CurrentDate) AS MonthNumber, 
    CAST(AVG(CurrentPrice) AS DECIMAL(18, 2)) AS Price
FROM
    dbo.Prices
GROUP BY 
    CurrentDate, DATENAME(month, CurrentDate), 
    DATENAME(year, CurrentDate), MONTH(CurrentDate)

I don't get one row with a average value of each month, instead I get a average value for each date. How can I change the query so I get one average value per month?

Data structure is like this....

CurrentDate                 CurrentPrice
-----------------------------------------
2022-09-06 00:00:00.000     59.81
2022-09-06 01:00:00.000     63.83

CodePudding user response：

Like Treuss stated get rid of the current date the grain will break your aggregation

SELECT        
    DATENAME(month, CurrentDate) AS MonthName, 
    DATENAME(year, CurrentDate) AS Year, 
    MONTH(CurrentDate) AS MonthNumber, 
    CAST(AVG(CurrentPrice) AS DECIMAL(18, 2)) AS Price
FROM
    dbo.Prices
GROUP BY 
 --   CurrentDate  --Omit this.  
 , DATENAME(month, CurrentDate)
 ,  DATENAME(year, CurrentDate)
 , MONTH(CurrentDate)