Sum a union query

Question

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.

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.

Solution 1

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

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

Solution 2

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

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

Solution 3

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

Also, you need to GROUP BY the inner queries

Solution 4

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:

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

And in your subquery, the having should probably be a where:

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

View more solutions

Comments

[OMG Ponies]

+1: Table aliases don't have to be used, but SQL Server does require they be defined for derived tables/inline views.

[ksh7]

Thank you!!! Adding the as T and T designation worked. Thank you everyone for your help with this.

[Ran Marciano]

Please don't post only code as an answer, but also provide an explanation of what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes.