1

I have the below query. IS there a way I can put this into one single query? If not, can I shrink it further anyway? Please advice.

    DECLARE @highRegion TABLE(regionId INT, countR INT)
    DECLARE @lowRegion TABLE(regionId INT, countR INT)
    DECLARE @midRegion TABLE(regionId INT, countR INT)

    INSERT INTO @highRegion
    SELECT c.fRegionID,
           COUNT(1) AS VALUE
    FROM   census.Country c
           INNER JOIN census.IncomeGroup ig
                ON  c.fIncomeGroupID = ig.IncomeGroupID
    WHERE  ig.Name IN ('High income: nonOECD', 'High income: OECD')
    GROUP BY
           c.fRegionID

    INSERT INTO @lowRegion
    SELECT c.fRegionID AS VALUE,
           COUNT(1)
    FROM   census.Country c
           INNER JOIN census.IncomeGroup ig
                ON  c.fIncomeGroupID = ig.IncomeGroupID
    WHERE  ig.Name = 'Upper middle income'
    GROUP BY
           c.fRegionID

    INSERT INTO @midRegion
    SELECT c.fRegionID,
           COUNT(1) AS VALUE
    FROM   census.Country c
           INNER JOIN census.IncomeGroup ig
                ON  c.fIncomeGroupID = ig.IncomeGroupID
    WHERE  ig.Name IN ('Lower middle income', 'Low income')
    GROUP BY
           c.fRegionID

    SELECT r.Name,
           r.NoOfCountries,
           h.countR AS HIGH,
           l.countR AS LOW,
           m.countR AS mid 
    FROM   census.Country c
           INNER JOIN @highRegion h
                ON  c.fRegionID = h.regionId
           INNER JOIN @lowRegion l
                ON c.fRegionID = l.regionId
           INNER JOIN @midRegion m
                ON c.fRegionID = m.regionId
           INNER JOIN census.Region r
                ON  c.fRegionID = r.RegionID
  • Is this for performance or just make the syntax shorter? – paparazzo Sep 9 '15 at 14:44
  • 2
    Why don't you give your income groups categories? Then you could do this a lot more "normally"... – Aaron Bertrand Sep 9 '15 at 16:13
6

Yes you should be able to do it, with something like this:

SELECT r.Name,
       r.NoOfCountries,
       sum(case when ig.Name IN ('High income: nonOECD', 'High income: OECD') then 1 else 0 end) AS HIGH,
       sum(case when ig.Name IN ('Lower middle income', 'Low income') then 1 else 0 end) AS LOW,
       sum(case when ig.Name = 'Upper middle income' then 1 else 0 end) AS mid 
FROM   census.Country c
       INNER JOIN census.Region r
            ON  c.fRegionID = r.RegionID
       INNER JOIN census.IncomeGroup ig
            ON  c.fIncomeGroupID = ig.IncomeGroupID
group by 
    r.Name,
    r.NoOfCountries

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.