Solved

Doing a union.

Posted on 2006-07-18
8
265 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-
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

740 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