Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

Doing a union.

I have a stored proc that we are using, the problem is that we want the results to be combined into one result.


ALTER PROCEDURE [dbo].[usr_sel_UnderwriterActivity_SubmissionCountByCompany]

AS

SET NOCOUNT ON

            Select C.CompanyName As 'Company Name', Count(A.QuoteID) As 'Submission Count'

            From [UnderwriterActivity] A

            Left Outer Join [BamOverrides_1] B

            On A.QuoteID = B.QuoteID

            Left Outer Join [Company] C

            On B.ReferenceID = C.ReferenceID

            Where C.CompanyName Is Not Null

            Group By C.CompanyName

           

            Union All

           

            Select C.CompanyName As 'Company Name', Count(A.QuoteID) As 'Submission Count'

            From [Erisk_History].[dbo].[UnderwriterActivity] A

            Left Outer Join [Erisk_History].[dbo].[BamOverrides_1] B

            On A.QuoteID = B.QuoteID

            Left Outer Join [Company] C

            On B.ReferenceID = C.ReferenceID

            Where C.CompanyName Is Not Null

            Group By C.CompanyName

           

            Order By C.CompanyName

0
-cr-
Asked:
-cr-
1 Solution
 
aprestoCommented:
Hi -cr-,

whats the problem? are you receiving an error?

Apresto
0
 
-cr-Author Commented:
No no error. I would just like one result instead of 2 seperate ones  simular to below. We currently have a whole grid with 2 rows as below for each item found in the query:


Company Name                                                              Submission Count
Acordia / Weisenberg Insurance & Risk Management      2  * This is from the first query
Acordia / Weisenberg Insurance & Risk Management      1  * This is from the second query
0
 
aprestoCommented:
ok try  this:

SELECT [Commpany Name], Count([SubmissionCount]) AS [Submission Count]
FROM
(
            Select C.CompanyName As 'Company Name', A.QuoteID As 'Submission Count'
            From [UnderwriterActivity] A
            Left Outer Join [BamOverrides_1] B
            On A.QuoteID = B.QuoteID
            Left Outer Join [Company] C
            On B.ReferenceID = C.ReferenceID
            Where C.CompanyName Is Not Null
            Group By C.CompanyName

            Union All

            Select C.CompanyName As 'Company Name', A.QuoteID As 'Submission Count'
            From [Erisk_History].[dbo].[UnderwriterActivi<wbr/>ty] A
            Left Outer Join [Erisk_History].[dbo].[BamOverrides_1] B
            On A.QuoteID = B.QuoteID
            Left Outer Join [Company] C
            On B.ReferenceID = C.ReferenceID
            Where C.CompanyName Is Not Null
            Group By C.CompanyName
            Order By C.CompanyName
) AS SummedTable
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
aprestoCommented:
>>>Count([SubmissionCount])

apologies, change the above in the first line to this:

Count([Submission Count])

with a space
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT [Company Name] , MAX([Submission Count]) [Submission Count]
FROM (
            Select C.CompanyName As 'Company Name', Count(A.QuoteID) As 'Submission Count'
            From [UnderwriterActivity] A
            Left Outer Join [BamOverrides_1] B On A.QuoteID = B.QuoteID
            Left Outer Join [Company] C        On B.ReferenceID = C.ReferenceID
            Where C.CompanyName Is Not Null
            Group By C.CompanyName

            Union All


            Select C.CompanyName As 'Company Name', Count(A.QuoteID) As 'Submission Count'
            From [Erisk_History].[dbo].[UnderwriterActivity] A
            Left Outer Join [Erisk_History].[dbo].[BamOverrides_1] B On A.QuoteID = B.QuoteID
            Left Outer Join [Company] C   On B.ReferenceID = C.ReferenceID
            Where C.CompanyName Is Not Null
            Group By C.CompanyName
) AS Tab
GROUP BY  [Company Name]
Order By [Company Name]
0
 
-cr-Author Commented:
aneeshattingal,
This works pretty well but it is not adding the totals. We are halfway there. Need to add the totals.

This is what your example returns:

Company Name                                                               Submission Count
Acordia / Weisenberg Insurance & Risk Management      2
Adco General Corporation                                             8
Adler-Downey of Arizona, Inc.                                            2



This is our original query:

Company Name                                                               Submission Count
Acordia / Weisenberg Insurance & Risk Management      2
Acordia / Weisenberg Insurance & Risk Management      1
Adco General Corporation                                             8
Adco General Corporation                                             5
Adler-Downey of Arizona, Inc.                                            1
Adler-Downey of Arizona, Inc.                                            2
0
 
wstuphCommented:
change

MAX([Submission Count]) [Submission Count]

to

sum([submission Count) [Submission Count]
0
 
-cr-Author Commented:
Excellent...!!!!
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Tackle projects and never again get stuck behind a technical roadblock.
Join Now