UNION query
-
I'm trying to do a UNION qry but am getting a syntax error on the union. I'm not sure why but seems to be something due to the order by. I'm trying to union a list of the top 5 companies and their premium with the subtotal premium of all the other companies. Any ideas would be helpful.
SELECT TOP 5 ProgramSummary.CedGroup, SUM(ProgramSummary.OPrem3) AS PremSum
FROM ProgramSummary LEFT OUTER JOIN
BSegs ON ProgramSummary.BSegID = BSegs.BSegID
WHERE (ProgramSummary.StatID = 2) AND (ProgramSummary.DtEff <= GETDATE()) AND (ProgramSummary.DtExp >= GETDATE()) AND
(BSegs.Division = 1) AND (BSegs.BranchKey = 10)
GROUP BY ProgramSummary.CedGroup
ORDER BY SUM(ProgramSummary.OPrem3) DESC
UNION SELECT 'Other Cos', SUM(ProgramSummary.OPrem3)
FROM ProgramSummary LEFT OUTER JOIN
BSegs ON ProgramSummary.BSegID = BSegs.BSegID
WHERE (ProgramSummary.StatID = 2) AND (ProgramSummary.DtEff <= GETDATE()) AND (ProgramSummary.DtExp >= GETDATE()) AND (BSegs.Division = 1) AND
(BSegs.BranchKey = 10) AND ProgramSummary.CedGroup Not In
(SELECT TOP 5 ProgramSummary.CedGroup
FROM ProgramSummary LEFT OUTER JOIN
BSegs ON ProgramSummary.BSegID = BSegs.BSegID
WHERE (ProgramSummary.StatID = 2) AND (ProgramSummary.DtEff <= GETDATE()) AND (ProgramSummary.DtExp >= GETDATE()) AND
(BSegs.Division = 1) AND (BSegs.BranchKey = 10)
GROUP BY ProgramSummary.CedGroup
ORDER BY SUM(ProgramSummary.OPrem3) DESC)
ORDER BY SUM(ProgramSummary.OPrem3)
-
remove the order by from the 1st SELECT statement and you should be good to go...
Good Hunting!AJ Ahrens
webmaster@kritter.net -
you can use order by clause tothe last query
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
Sukhoi,
The ORDER BY is in both SELECTS already. The code will work by removing the 1st only ONLY
Good Hunting!AJ Ahrens
webmaster@kritter.net
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply