Combine join request

Question

I use Microsoft SQL Svr Mgmt Studio 2008. I do not have access to create a temporary table (the company limits the ability to create or modify tables), or I would use this to solve this problem.

I have successfully used the join query to combine the results of the three selected queries. Now I am trying to summarize the results of the union.

When I execute the request below, I get:

Incorrect syntax near the keyword 'GROUP' 

And then when I delete the group, I get:

 Incorrect syntax near ')' 

Here is my request:

 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 could not find a solution to this question on any of the previous questions.

Answer 1

You need to execute an alias of your view, you must also use a group with a condition of availability.

 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 

Answer 2

SQL Server requires that you define a table alias for the 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 

Answer 3

I do not have access to create a temporary table (the company limits the ability to create or modify tables) or I would use this to solve this problem. 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 execute most of the information that a temporary table can use.

Like Martin (now deleted), type in a sentence, consider giving a subquery to aliases, for example:

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

And in your having subquery should be where :

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

Answer 4

Change your first line to

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

and the last line is

 ) T GROUP BY T.Period, T.PCC 

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

In addition, you need GROUP BY internal queries