CODEWITHSUNDEEP
sqlaggregate-functions
Rick Walker
Rick Walker

Reputation: 97

Way to eliminate DISTINCT in this query

This query returns the correct SUM, but its duplicating itself:

SELECT [GrandTotal] = (total1 + total2)
FROM (SELECT(SELECT SUM([Total]) [total1] FROM [ooc].[RentalRevenue2] WHERE [CheckOut] >= '2015-01-01' AND [CheckOut] < '2015-06-30' AND [CampaignName] NOT LIKE '%Non-Cancellable%') as total1,
            (SELECT SUM([Total]) [total2] FROM [ooc].[Revenue2] WHERE [DateConfirmationReceived] >= '2015-01-01' AND [DateConfirmationReceived] < '2015-06-30' AND [CampaignName] LIKE '%Non-Cancellable%') as total2
      FROM [ooc].[Revenue2]) T

I can eliminate this behavior by adding DISTINCT.

SELECT DISTINCT [GrandTotal] = (total1 + total2)
FROM (SELECT(SELECT SUM([Total]) [total1] FROM [ooc].[RentalRevenue2] WHERE [CheckOut] >= '2015-01-01' AND [CheckOut] < '2015-06-30' AND [CampaignName] NOT LIKE '%Non-Cancellable%') as total1,
            (SELECT SUM([Total]) [total2] FROM [ooc].[Revenue2] WHERE [DateConfirmationReceived] >= '2015-01-01' AND [DateConfirmationReceived] < '2015-06-30' AND [CampaignName] LIKE '%Non-Cancellable%') as total2
      FROM [ooc].[Revenue2]) T

I don't think this is the correct way of executing this SUM query.

Upvotes: 0

Views: 43

Answers (2)

siride
siride

Reputation: 209585

Your two subqueries that perform SUM operations don't appear to have any relationship to the SELECT ... FROM [ooc].[Revenue2] query that contains them. But, because they are located in that construct, there will be one row in the output for every row in [ooc].[Revenue2]. Based on what I see here, you can safely simplify the query to the following:

SELECT [GrandTotal] = 
       (SELECT SUM([Total]) [total1] 
          FROM [ooc].[RentalRevenue2] 
         WHERE [CheckOut] >= '2015-01-01' 
           AND [CheckOut] < '2015-06-30'
           AND [CampaignName] NOT LIKE '%Non-Cancellable%') +
       (SELECT SUM([Total]) [total2]
          FROM [ooc].[Revenue2]
         WHERE [DateConfirmationReceived] >= '2015-01-01' 
           AND [DateConfirmationReceived] < '2015-06-30'
           AND [CampaignName] LIKE '%Non-Cancellable%')

As pointed out by @Quassnoi, if either of these subqueries results in no rows, the final result will be NULL. This can be avoided with ISNULL(..., 0) wrapped around each subquery. I will leave this up to the OP as they may know whether such a result is even possible.

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425341

SELECT  SUM(Total)
FROM    (
        SELECT  total
        FROM    Revenue2
        WHERE   DateConfirmationReceived >= '2015-01-01'
                AND DateConfirmationReceived < '2015-06-30'
                AND CampaignName LIKE '%Non-Cancellable%'
        UNION ALL
        SELECT  total
        FROM    RentalRevenue2
        WHERE   checkout >= '2015-01-01'
                AND checkout < '2015-06-30'
                AND CampaignName NOT LIKE '%Non-Cancellable%'
        ) q

Upvotes: 1

Related Questions