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

CodePudding user response：

There's a few things that could be improved on your general design, but as is you can accomplish it via this query:

select 'North America' Continent
    , t.Country
    , sum(t.amount) sumAmount
from table
where t.Country is not null
group by t.Country

You won't be able to select ID as you need to group, and that's going to ruin your math. Since you just want all of your Continents to be North America, you can just put in a static value.

However, I think you are looking to group on Order rather than ID. You can do that like so:

select t.[Order]
    , 'North America' Continent
    , t.Country
    , sum(t.amount) sumAmount
from t
where t.Country is not null
group by  t.[Order], t.Country
order by t.[Order]

Again, I recommend a change in table design, and you may want to consider a change in practices if you always want Continent to be North America. What if you change your mind in the future?

CodePudding user response：

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):

• Example db<>fiddle