Solved

Doing a union.

Posted on 2006-07-18
8
262 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
ID: 17131482
Hi -cr-,

whats the problem? are you receiving an error?

Apresto
0
 

Author Comment

by:-cr-
ID: 17131550
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
ID: 17131588
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 23

Expert Comment

by:apresto
ID: 17131597
>>>Count([SubmissionCount])

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

Count([Submission Count])

with a space
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 17131633
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-
ID: 17132367
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
ID: 17132421
change

MAX([Submission Count]) [Submission Count]

to

sum([submission Count) [Submission Count]
0
 

Author Comment

by:-cr-
ID: 17132460
Excellent...!!!!
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL LINE CONTINUATION ISSUE 12 33
How to search for strings inside db views 4 27
SQL Error - Query 6 25
insert wont work in SQL 14 21
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

770 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