Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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