关键字'GROUP'IN SQL附近的语法错误，同时取平均值[重复]

This question already has an answer here:

这个问题在这里已有答案:

Incorrect syntax near the keyword 'GROUP' IN SQL 3 answers

关键字“GROUP”IN SQL 3答案附近的语法不正确

I am facing an error while grouping the following 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),
         DATENA ME(weekday,date_time)
ORDER BY DATEPART(YEAR,date_time),
         DATEPART(MONTH,date_time),
         CONVERT(date,date_time)
SELECT @avg_volume

My inner query will return

我的内部查询将返回

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

My primary table is :

我的主要表是:

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

My desired output is:

我想要的输出是:

station id     year  month     weekday    average_volume
  7            2013     2      Thursday     194

3 个解决方案

#1

There are 3 issues in query you have posted:

您发布的查询中有3个问题:

As pointed by everyone correct identifier DATENA ME to DATENAME.

正如每个人所指出的正确标识符DATENA ME to DATENAME。

When a inner query in defined in From clause of an outer query it is actually a 'Derived Table'.Their scope of existence is the outer query. As soon as the outer query is finished, the derived table is gone. So we need to specify the query that defines the derived table within parentheses, followed by the AS clause and the derived table name. (Below I have written it as 'T')

当外部查询的From子句中定义的内部查询实际上是“派生表”时。它的存在范围是外部查询。外部查询完成后,派生表就会消失。因此,我们需要指定在括号内定义派生表的查询,后跟AS子句和派生表名。 (下面我把它写成'T')

Now as we are referring to columns from a derived table in outer query only the columns defined in it can be accessed. ( So I have corrected the outer query columns)

现在我们在外部查询中引用派生表中的列,只能访问其中定义的列。 (所以我更正了外部查询列)

Write as:

--DECLARE @avg_volume int
SELECT ISNULL(AVG(Total_Volume),0) as average_volume,
         station_id,
         MONTH,
         YEAR,
         weekday
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)) AS T
WHERE WEEKDAY = 'Thursday' AND MONTH=2
GROUP BY station_id,
         MONTH,
         YEAR,
         weekday
ORDER BY YEAR,
         MONTH

SQL FIDDLE: http://sqlfiddle.com/#!3/6217d/10

SQL FIDDLE:http://sqlfiddle.com/#!3/6217d/10

#2

There is a space in your code DATENAME

您的代码DATENAME中有一个空格

DATENA ME(weekday,date_time)

Write as

DATENAME(weekday,date_time)

------------------------Another point--------------------------

you must use name for for your subquery like

你必须使用你的子查询的名称

SELECT t1.yourColumn
(
select yourColumn
FROM tableABC
) t1 <you not assign name here to newly subquery created table e.g t1>

Also use column aliases like

也可以使用列别名

SELECT t1.aliasName
(
select yourColumn as [aliasName] 
FROM tableABC
) t1
ORDER BY t1.aliasName

#3

Give alias name to your inner query and then make use of that alias name in your outer table group by statement

为内部查询指定别名,然后在外部表group by语句中使用该别名

(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)
) AS MYJUNCTIONTABLE

Also I found spaces between DATE NA ME while copying your code, see its corrected above.

此外,我在复制代码时在DATE NA ME之间找到了空格,请参阅上面的更正。

#1