lundi 29 juin 2015

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.

Aucun commentaire:

Enregistrer un commentaire