I am facing an error while grouping a statement. Here is my code
DECLARE @avg_volume INT
SELECT @avg_volume = ISNULL(AVG(Total_Volume), 0)
FROM ( SELECT station_id ,
DATEPART(Year, date_time) AS YEAR ,
DATEPART(month, date_time) AS MONTH ,
CONVERT(DATE, date_time) AS DATE ,
DATENAME(weekday, date_time) AS weekday ,
SUM(volume) AS Total_volume
FROM rvtcs_data_aggregated_hourly
GROUP BY station_id ,
CONVERT(DATE, date_time) ,
DATEPART(month, date_time) ,
DATEPART(Year, date_time) ,
DATENAME(weekday, date_time)
)
GROUP BY station_id ,
CONVERT(DATE, date_time) ,
DATEPART(month, date_time) ,
DATEPART(Year, date_time) ,
DATENAME(weekday, date_time)
ORDER BY DATEPART(Year, date_time) ,
DATEPART(month, date_time) ,
CONVERT(DATE, date_time)
SELECT @avg_volume
What I am trying to do is ,taking the sum from volume group by a set of conditions(Inner query), which will give result as
station_id YEAR MONTH DATE weekday Total_volume
7 2013 2 2013-02-21 Thursday 192
7 2013 2 2013-02-27 Wednesday 2699
7 2013 2 2013-02-28 Thursday 196
2 2013 3 2013-03-07 Thursday 192
7 2013 3 2013-03-07 Thursday 192
now I want to take average of that. Any assistance will be helpful.
My Table primary table
station_id date_time volume
7 2013-02-21 00:00:00.000 96
7 2013-02-21 01:00:00.000 96
7 2013-02-27 00:00:00.000 356
7 2013-02-27 00:00:00.000 410
7 2013-02-27 00:00:00.000 471
7 2013-02-27 00:00:00.000 530
7 2013-02-27 00:00:00.000 338
7 2013-02-27 00:00:00.000 211
7 2013-02-27 00:00:00.000 159
7 2013-02-27 00:00:00.000 128
7 2013-02-27 00:00:00.000 96
7 2013-02-28 00:00:00.000 96
7 2013-02-28 01:00:00.000 100
7 2013-03-07 00:00:00.000 96
2 2013-03-07 00:00:00.000 96
2 2013-03-07 01:00:00.000 96
7 2013-03-07 01:00:00.000 96
Desired Output :
station id year month weekday average_volume
7 2013 2 Thursday 194
There are few fundamental issues in your query. You are summing up and finding average on basis of month year Date ETC. I do not see any filters, for a year or month or an station id. So I assume there will be more than one row in your final select query and you are selecting into a variable. So you may get only the value from the first/last row in the variable(Of course depending on the ordering) .You have to make some modification to the query here.
Is this query giving you correct output?
Based on what you want to do, you don't need to do a second explicit aggregation or
order by
. Just take the average:Your specific error was because you had no alias on the subquery (well, the keyword
group
doesn't count as an alias).Some of your column names a keywords, so I quoted those. The list of such words is here.
Actually, when writing a query like this, you don't need to include all the columns in the
select
, so you could also do:You need to name your subquery: