Solved

Doing a union.

Posted on 2006-07-18
8
259 Views
Last Modified: 2012-06-27
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
Comment
Question by:-cr-
8 Comments
 
LVL 23

Expert Comment

by:apresto
Comment Utility
Hi -cr-,

whats the problem? are you receiving an error?

Apresto
0
 

Author Comment

by:-cr-
Comment Utility
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
 
LVL 23

Expert Comment

by:apresto
Comment Utility
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
 
LVL 23

Expert Comment

by:apresto
Comment Utility
>>>Count([SubmissionCount])

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

Count([Submission Count])

with a space
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
Comment Utility
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
 

Author Comment

by:-cr-
Comment Utility
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
 
LVL 12

Expert Comment

by:wstuph
Comment Utility
change

MAX([Submission Count]) [Submission Count]

to

sum([submission Count) [Submission Count]
0
 

Author Comment

by:-cr-
Comment Utility
Excellent...!!!!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now