CODEWITHSUNDEEP
sqlsql-server
Oasis
Oasis

Reputation: 80

Sum of column depending on values

Can you guys let me know how to make a query that output the sum of amount based on column values(order, Continent and Country)? Also, I want to show all Continent values as unique value (North America)

Example table,

ID    Code   Continent       Country          amount
----------------------------------------------------
1       1     North America   NULL               NULL
2       1     America         USA                10
3       1     NA              USA                10
4       1     Unknown         USA                10
5       2     North America   NULL               NULL
6       2     America         Canada             15
7       2     NA              Canada             15  
8       2     Unknown         Canada             15
9       3     North America   NULL               NULL
10      3     America         Mexico             20
11      3     NA              Mexico             20  
12      3     Unknown         Mexico             20

Output

ID  Code  Continent       Country       SumAmount
----------------------------------------------
1     1    North America     USA            30
2     2    North America     Canada         45
3     3    North America     Mexico         60

I have tried to approach it like

select ID, Code, case when Continent != 'North America' then Continent = 'North America' end as Continent, Country, sum(Amount) as SumAmount
from Table group by ID, Continent, Country

or maybe I need to make a query like this and work with this query below?

select ID, Code, Continent, Country, sum(Amount) as SumAmount
from Table where Continent !='North America'

But it is not working. How should I do this?

I appreciate for any other approaches. It would be better than mine

Upvotes: 1

Views: 126

Answers (2)

SteveC
SteveC

Reputation: 6015

The simplest query which returns the correct result seems to be something like this

select row_number() over (order by Code) ID, 
       Code, 
       'North America' Continent, 
       Country, 
       sum(amount) SumAmount
from dbo.TableName
where Country is not null
group by Code, Country
order by Code;

dbFiddle

Upvotes: 1

anon
anon

Reputation:

The awkward design here (relations with no real indication of such other than the shared Code column) is going to lead to suboptimal queries like this

DECLARE @ContinentToReport varchar(32) = 'North America';

;WITH x AS 
(
  SELECT Code FROM dbo.TableName 
  WHERE Continent = @ContinentToReport 
    AND Country IS NULL
)
SELECT ID = ROW_NUMBER() OVER (ORDER BY x.Code), 
       x.Code, 
       Continent = @ContinentToReport, 
       t.Country, 
       SumAmount = SUM(t.amount)
  FROM dbo.TableName AS t
  INNER JOIN x ON t.Code = x.Code
  WHERE t.Country IS NOT NULL
  GROUP BY x.Code, t.Country
  ORDER BY x.Code;

Output (though I made a guess at what ID means and why it's different then the ID and the source, and I find the Continent column is kind of redundant since it will always be the same):

ID Code Continent Country SumAmount
1 1 North America USA 30
2 2 North America Canada 45
3 3 North America Mexico 60

Upvotes: 3

Related Questions