SQL 对联合查询求和

Sum a union query

提问by ksh7

I'm using Microsoft SQL Svr Mgmt Studio 2008. I don't have access to create a temporary table (company restricts ability to create or modify tables) or I would use that to solve this problem.

我正在使用 Microsoft SQL Svr Mgmt Studio 2008。我无权创建临时表（公司限制创建或修改表的能力），或者我会用它来解决这个问题。

I have successfully used a union query to combine the results of three select queries. Now I am trying to sum the results of the union.

我已经成功地使用联合查询来组合三个选择查询的结果。现在我试图总结联合的结果。

When I execute the query below I receive:

当我执行下面的查询时，我收到：

Incorrect syntax near the keyword 'GROUP'

And then when I remove the group by I get:

然后当我删除组时，我得到：

Incorrect syntax near ')'

Here's my query so far:

到目前为止，这是我的查询：

Select Period, PCC, SUM(BasicHits), SUM(FareHits), SUM(SearchHits)
From (

SELECT     AAAPeriod AS Period, 
AAAFromPCC AS PCC, 
- SUM(AAABasic) AS BasicHits,
- SUM(AAAFare) AS FareHits,
- SUM(AAASearch) AS SearchHits
FROM        HitsAaa
HAVING     (AAAPeriod = N'2010-10') 

UNION ALL

SELECT     AAAPeriod, 
AAAtoPCC,
SUM(AAABasic),
SUM(AAAFare),
SUM(AAASearch) 
FROM        HitsAaa
HAVING     (AAAPeriod = N'2010-10')

UNION ALL

SELECT      AgtPeriod, 
AgtPcc, 
SUM(AgtBasic),
SUM(AgtFare),
SUM(AgtSearch)
FROM          HitsAgent
HAVING      (AgtPeriod = N'2010-10')

)GROUP BY Period, PCC

I haven't been able to find a solution to this on any of the previous questions.

对于之前的任何问题，我都无法找到解决方案。

回答by John Hartsock

You need to alias your derived table, you must also use a group by with a having clause.

您需要为派生表设置别名，还必须使用带有 have 子句的 group by。

SELECT
  q1.Period, 
  q1.PCC, 
  SUM(q1.BasicHits), 
  SUM(q1.FareHits), 
  SUM(q1.SearchHits)
FROM (SELECT     
        AAAPeriod AS Period, 
        AAAFromPCC AS PCC, 
        - SUM(AAABasic) AS BasicHits,
        - SUM(AAAFare) AS FareHits,
        - SUM(AAASearch) AS SearchHits
      FROM HitsAaa
      GROUP BY 
        AAAPeriod, 
        AAAFromPCC
      HAVING (AAAPeriod = N'2010-10') 

      UNION ALL

      SELECT     
        AAAPeriod AS Period, 
        AAAtoPCC AS PCC,
        SUM(AAABasic) AS BasicHits,
        SUM(AAAFare) AS FareHits,
        SUM(AAASearch) AS SearchHits 
      FROM HitsAaa
      GROUP BY 
        AAAPeriod, 
        AAAtoPCC
      HAVING (AAAPeriod = N'2010-10')

      UNION ALL

      SELECT
        AgtPeriod AS Period, 
        AgtPcc AS PCC, 
        SUM(AgtBasic) AS BasicHits,
        SUM(AgtFare) AS FareHits,
        SUM(AgtSearch)  AS SearchHits
      FROM HitsAgent
      GROUP BY 
        AgtPeriod, 
        AgtPCC
      HAVING (AgtPeriod = N'2010-10')) q1 
GROUP BY 
  q1.Period, 
  q1.PCC

回答by OMG Ponies

SQL Server requires that you define a table alias for a derived table/inline view:

SQL Server 要求您为派生表/内联视图定义表别名：

SELECT x.period, x.pcc, SUM(x.BasicHits), SUM(x.FareHits), SUM(x.SearchHits)
  FROM (SELECT AAAPeriod AS Period, 
               AAAFromPCC AS PCC, 
               - SUM(AAABasic) AS BasicHits,
               - SUM(AAAFare) AS FareHits,
               - SUM(AAASearch) AS SearchHits
          FROM HitsAaa
         WHERE AAAPeriod = N'2010-10'
      GROUP BY aaaperiod, aaafrompcc
        UNION ALL
        SELECT AAAPeriod, 
               AAAtoPCC,
               SUM(AAABasic),
               SUM(AAAFare),
               SUM(AAASearch) 
          FROM HitsAaa
         WHERE AAAPeriod = N'2010-10'
      GROUP BY aaaperiod, aaafrompcc
        UNION ALL
        SELECT AgtPeriod, 
               AgtPcc, 
               SUM(AgtBasic),
               SUM(AgtFare),
               SUM(AgtSearch)
          FROM HitsAgent
         WHERE AgtPeriod = N'2010-10'
      GROUP BY agtperiod, agtpcc) AS x
GROUP BY x.period, x.pcc

回答by Andomar

I don't have access to create a temporary table (company restricts ability to create or modify tables) or I would use that to solve this problem.

我无权创建临时表（公司限制创建或修改表的能力），或者我会用它来解决这个问题。

Instead of a temporary table, try using a table variable:

尝试使用表变量而不是临时表：

declare @t table (id int primary key, col1 varchar(50))
insert @t (col1) values ('hello table variable')
select * from @t

A table variable can do most of the things a temporary table can.

表变量可以做临时表可以做的大部分事情。

Like Martin's (now deleted) answer suggests, consider giving the subquery an alias, like:

就像 Martin（现已删除）的答案所建议的那样，考虑给子查询一个别名，例如：

select  ... list of columns ...
from    (
        ... subquery ...
        ) as SubQueryAlias
group by
        col1

And in your subquery, the havingshould probably be a where:

在您的子查询中，having应该可能是where：

...
FROM        HitsAaa
WHERE       (AAAPeriod = N'2010-10') 
...

回答by Jose Rui Santos

Change your first line to

将您的第一行更改为

Select T.Period, T.PCC, SUM(T.BasicHits), SUM(T.FareHits), SUM(T.SearchHits)

and the last line to

和最后一行

) T GROUP BY T.Period, T.PCC

You need to define a table alias (in this case T) for inner tables

您需要为内部表定义一个表别名（在本例中为 T）

Also, you need to GROUP BYthe inner queries

此外，您需要GROUP BY内部查询