SQL Server Max Value of the Sum of Multiple Columns

2ul0zpep 于 2023-08-02 发布在其他

关注(0)|答案(2)|浏览(113)

I am using one table in SQL Server.

This is how the table is displayed originally:
| ItemNo | StoreNo | DemandYr1 | DemandYr2 | DemandYr3 |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | AA | 2 | 3 | 0 |
| 1 | BB | 5 | 1 | 2 |
| 5 | BB | 5 | 5 | 5 |
| 5 | CC | 0 | 0 | 6 |
| 3 | AA | 2 | 1 | 4 |
| 3 | BB | 1 | 0 | 3 |
| 3 | CC | 2 | 2 | 2 |

After using this query,

select itemno, storeno, sum(DemandYr1 + DemandYr2 + DemandYr3) as SumDemand
from ExTable 
group by itemno, storeno
order by itemno asc

I have gotten the table to this:

ItemNo	StoreNo	SumDemand
1	AA	5
1	BB	8
5	BB	15
5	CC	6
3	AA	7
3	BB	4
3	CC	6

Now, my desired result is to only show the storeno with the highest SumDemand for each ItemNo in a table like this:

ItemNo	StoreNo	SumDemand
1	BB	8
5	BB	6
3	CC	6

Since the SumDemand column is not part of the original table and was added by me, I am not sure how to query it to only show the greatest SumDemand for each ItemNo. I understand that if two StoreNo's have the same SumDemand there will be duplicates, that is fine.

** If possible, I would also like to omit any results that = 0. Ex: If an Item No has 0 SumDemand at all stores, I would like to delete. (If this is not possible that is also fine) **

sql-server

来源：https://stackoverflow.com/questions/76716901/max-value-of-the-sum-of-multiple-columns

2条答案

按热度按时间

t9eec4r01#

You can add a row_number to your select in combination with a CTE.

But you should avoid adding spaces in your column names as the must all be escaped

WITH CTE as (
select [item no], [store no], sum([Demand Yr1] + [Demand Yr2] + [Demand Yr3]) as SumDemand
  ,ROW_NUMBER() OVER(PARTITION BY [Item No] ORDER BY  sum([Demand Yr1] + [Demand Yr2] + [Demand Yr3]) DESC ) rn
from ExTable 
group by [item no], [store no])
  SELECT [item no], [store no],SumDemand
  FROM CTE WHERE rn = 1
order by [item no] asc

item no	store no	SumDemand
1	BB	8
3	AA	7
5	BB	15

Fiddle

赞(0）回复(0）举报 2023-08-02

bf1o4zei2#

The way you approach this is to use a windowed count; also here the addition is moved to a cross-apply so it can be re-used.

select itemno, storeno, SumDemand
from (
  select itemno, storeno, Sum(Demand) SumDemand, 
    Row_Number() over(partition by itemno order by Sum(demand) desc) rn
  from t
  cross apply(values(DemandYr1 + DemandYr2 + DemandYr3))d(Demand)
  group by itemno, storeno
)t
where rn = 1
order by itemno;

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Max Value of the Sum of Multiple Columns

2条答案

相关问题

热门标签

最新问答