none
Combine 2 queries into 1

    Question

  • Is it possible to combine these 2 queries into 1:

    SELECT Distinct Campaigns.CampaignName,(select Distinct count(Taxis.Region) from Taxis WHERE Taxis.Value1=34) as total
    FROM Taxis 
    INNER JOIN Campaigns ON 
    Taxis.Value1 = Campaigns.CampaignID 
    WHERE Taxis.Value1=34
    
    SELECT DISTINCT Region,count(*)
    FROM Taxis
    where Value1 = 34
    group by Region

    Tuesday, July 16, 2013 6:28 AM

Answers

  • Try union:

    SELECT * 
    FROM
    (SELECT Distinct Campaigns.CampaignName,(select Distinct count(Taxis.Region) from Taxis WHERE Taxis.Value1=34) as total
    FROM Taxis 
    INNER JOIN Campaigns ON 
    Taxis.Value1 = Campaigns.CampaignID 
    WHERE Taxis.Value1=34
    
    UNION
    
    SELECT DISTINCT Region,count(*)
    FROM Taxis
    where Value1 = 34
    group by Region
    )Temp


    Regards Harsh

    • Marked as answer by C-Sharp Mamba Tuesday, July 16, 2013 7:38 AM
    Tuesday, July 16, 2013 7:28 AM

All replies

  • Please post table definition for CAMPAIGNS AND TAXIS. It will be easy to answer.

    Why cann't we write simply,

    SELECT a.CompaignName,b.region, Count(b.Region) as Total FROM TAXIS b INNER JOIN CAMPAIGN a
    ON a.CampaignID = b.Value1
    WHERE b.Value1 = 34
    GROUP BY a.CompaignName,b.region


    Regards, RSingh



    Tuesday, July 16, 2013 6:38 AM
  • Try union:

    SELECT * 
    FROM
    (SELECT Distinct Campaigns.CampaignName,(select Distinct count(Taxis.Region) from Taxis WHERE Taxis.Value1=34) as total
    FROM Taxis 
    INNER JOIN Campaigns ON 
    Taxis.Value1 = Campaigns.CampaignID 
    WHERE Taxis.Value1=34
    
    UNION
    
    SELECT DISTINCT Region,count(*)
    FROM Taxis
    where Value1 = 34
    group by Region
    )Temp


    Regards Harsh

    • Marked as answer by C-Sharp Mamba Tuesday, July 16, 2013 7:38 AM
    Tuesday, July 16, 2013 7:28 AM
  • You are a legend thank you, can't believe that didn't even enter my head...
    Tuesday, July 16, 2013 7:37 AM
  • If possible use UNION ALL instead of UNION

    Krishnakumar S

    Tuesday, July 16, 2013 7:40 AM
  • Union all includes duplicates also ,only this is the difference between union and union all. Better to use UNION. 
    Tuesday, July 16, 2013 8:00 AM
  • You can't say that UNION is better than UNION ALL because it depends on your requirement and your data.

    Generally UNION ALL is faster than UNION so if you know that duplicates isn't an issue, then I'd use UNION ALL.


    Steen Schlüter Persson (DK)

    Tuesday, July 16, 2013 9:22 AM
  • and also if the number of rows are large, then the Sort physical operator used for UNION may be resource intensive..

    Krishnakumar S

    Tuesday, July 16, 2013 9:27 AM