Stuck on SQL server nested queries/cte

iecba09b 于 2023-05-16 发布在 SQL Server

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

I want it to display running percentage of each sum of profit grouped by state where sum of profit per state > 0. So no deficit states should be there.

The data I am working on looks like this:

I tried various different code to achieve that, none of them worked for me. Some of them were close to what I needed some of them were not even close. My most recent one looks like this:

select state, sum_of_profit, sum_of_profit/sum(sum_of_profit) as perc
from        (select state, sum(profit) as sum_of_profit 
            from superstore
            group by state
            having sum(profit) >= 0 
            ) as temp
group by state, sum_of_profit
order by sum_of_profit desc

sql-server

来源：https://stackoverflow.com/questions/76190199/stuck-on-sql-server-nested-queries-cte

2条答案

按热度按时间

8i9zcol21#

To display running percentage of each sum of profit, then we have to group by sum_of_profit only

select sum_of_profit, sum_of_profit/sum(sum_of_profit) as perc
from ( 
      select state, sum(profit) as sum_of_profit 
      from superstore
      group by state
      having sum(profit) >= 0 
) as temp
group by sum_of_profit
order by sum_of_profit desc

赞(0）回复(0）举报 2023-05-16

7fyelxc52#

select state,city,sum_of_profit,Total,(
                case when sum_of_profit=0 then 1 else sum_of_profit end
                /
                case when Total=0 then 1 else Total end
            ) *100 perc 
from (
    select *,
             sum(profit) over(partition by state ) as sum_of_profit,
             sum(profit) over(order by (select 0) )  Total 
             ,ROW_NUMBER() over(partition by state order by state) as rw
    from superstore
)a
where a.rw=1

Base Data:

create table superstore( state varchar(100),city varchar(100),Profit float)
insert into superstore(state,city,Profit) values
('H','K', 41.91),('H','K',219.58  ),('l','c',6.87),('f','f',383.03)
,('f','f',2.52),('l','c',14.17),('l','c',1.97),('l','c',90.72),('l','c',5.78),('l','c',34.47)

赞(0）回复(0）举报 2023-05-16

我来回答

Stuck on SQL server nested queries/cte

2条答案

相关问题

热门标签

最新问答