Incorrect syntax near the keyword 'GROUP'

问题:

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

回答1:

You need to name your subquery:

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)

        ) AnyNameYouLikeButYouHaveToGiveOne --<-- Here

 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

回答2:

Based on what you want to do, you don't need to do a second explicit aggregation or order by. Just take the average:

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)
        ) t;

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:

DECLARE @avg_volume INT
SELECT  @avg_volume = ISNULL(AVG(Total_Volume), 0)
FROM    ( SELECT    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)
        ) t;

回答3:

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?

SELECT  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)
          ORDER BY  DATEPART(year, date_time) ,
                    DATEPART(month, date_time) ,
                    CONVERT(DATE, date_time)
        ) T;