CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Doiremik
Doiremik

Reputation: 265

Use Sum in certain conditions

I have say the following rows

Country Population
IE  30
IE  20
UK  15
DE  20
DE  10
UK  20
BE  5

So basically I want to net the values together only for IE and DE... the rest I just want the values

So this would sum them all ..

Select Country, Sum(Population) From CountryPopulation group by Country

and I can add a where clause to exclude all other countries except IE and DE... but I also want these in the result set but just not summed.

So the table above would look like this when summed

Country Population
IE  50         -- Summed
UK  15         -- Orginal Value
DE  30         -- Summed
UK  20         -- Orginal Value
BE  5          -- Orginal Value

Problem is I can’t get a sum if, or case to work as the query has to be aggregated by group by. Only other way I can thing on is to

Sum all the IE and DE and union it with the rest of the data..
Or
Maybe use a CTE

Is there a nice slick way of doing this....

Upvotes: 2

Views: 86

Answers (3)

Tim Schmelter
Tim Schmelter

Reputation: 460048

You could also use UNION ALL and divide this query into two:

SELECT P.country, 
       P.population 
FROM  (SELECT country, 
              Population = Sum(population) 
       FROM   dbo.countrypopulation cp 
       WHERE  country IN ( 'IE', 'DE' ) 
       GROUP  BY country 

       UNION ALL 

       SELECT country, population 
       FROM   dbo.countrypopulation cp 
       WHERE  country NOT IN ( 'IE', 'DE' )
      ) P 
ORDER  BY P.population DESC

Even if this is not so concise it is readable and efficient.

sql-fiddle

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

declare @t table (Country char(2), Population int)
insert into @t (Country, Population) values
('IE',30),
('IE',20),
('UK',15),
('DE',20),
('DE',10),
('UK',20),
('BE',5 )

; With Ordered as (
    select Country,Population,CASE
                                 WHEN Country in ('IE','DE') THEN 1
                                 ELSE ROW_NUMBER() OVER (ORDER BY Country)
                               END as rn
    from @t
)
select Country,rn,SUM(Population)
from Ordered
group by Country,rn

Produces:

Country rn                   
------- -------------------- -----------
BE      1                    5
DE      1                    30
IE      1                    50
UK      6                    15
UK      7                    20

The trick is to just introduce a unique value for each row, except for the IE and DE rows that all get a 1. If the source rows all, actually, already have such a unique value then the CTE can be simplified (or avoided, at the expense of having to place the CASE expression in the GROUP BY as well as the SELECT)

Upvotes: 2

juergen d
juergen d

Reputation: 204746

Select Country, Sum(Population) 
From CountryPopulation 
group by case when Country in ('IE','DE')
              then 'IE_DE'
              else Country
         end

Upvotes: 4

Related Questions