Use Sum in certain conditions

Question

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....

juergen d · Accepted Answer · 2013-10-29 15:19:50Z

4

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

answered Oct 29, 2013 at 15:19

juergen d

205k40 gold badges305 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Damien_The_Unbeliever Over a year ago

I don't think the OP wants to merge together the sums for IE and DE - I think they just want one row for IE, one row for DE, and then all other countries should have as many rows as they do in the source table.

Doiremik Over a year ago

thats correct Damien... I want to sum them by country and not combine them... but like the idea of a group in the case ;-)

Damien_The_Unbeliever · Accepted Answer · 2013-10-29 15:24:28Z

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)

Tim Schmelter · Accepted Answer · 2013-10-29 15:48:59Z

1

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

edited Oct 29, 2013 at 15:48

answered Oct 29, 2013 at 15:27

Tim Schmelter

462k79 gold badges721 silver badges981 bronze badges

1 Comment

Doiremik Over a year ago

thanks Tim... yes this was the only one that I could come up with myself but I hate repeating SQL and wanted to see was there a more technical solution... thanks again

Collectives™ on Stack Overflow

Use Sum in certain conditions

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related