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.
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
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
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')
...
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With