Windowed functions cannot be used in the context of another windowed function or aggregate.

Analyst_SQL 3,531 Reputation points
2021-11-21T08:07:17.987+00:00

i am trying to sum of Column then below error is coming.

with cte as(
SELECT   C.ContNo,
--C.Rag_Type,

   case when cat.Cat_name='Savers' then 'A' else r.R_type_name END Rag_Type,

C.entrydate as EntryDate, C.ConWeight as Act_weight,  C.No_Of_Bales as Act_Qty,iSNULL(Sum(I.R_Weight),0)Isu_Weight,iSNULL(Sum(i.R_QTY),0)Isu_QTY,(C.ConWeight-iSNULL(Sum(I.R_Weight),0)) as Pending_Weight,
(C.No_Of_Bales-iSNULL(Sum(i.R_QTY),0)) as Pending_Qty
FROM     Containerno C
inner join ConCatagory cat on cat.Cat_ID=c.Cat_ID
inner   join tbl_ContD CD on CD.CID=C.CID
left   join tbl_Issuance_Rags I on I.D_ID=CD.D_ID
left join tbl_Range as r on (c.con_value >= r.R_Value_From AND C.con_value <= r.R_Value_to)

where C.delid is null and I.Delid is null and C.entrydate>'2020-12-31'  and c.con_status is null
group by  C.ContNo,C.Contrackno, C.ConWeight,C.No_Of_Bales,C.entrydate,cat.Cat_name,r.R_type_name
),
cte2 as(
select Rag_Type Type ,sum(Pending_Weight) Weight from cte
where Pending_Qty>0 and EntryDate>'2020-12-31'
group by Rag_Type)

select Type,Weight,
ISNULL(cast((Weight)*100.0/(sum(Weight)over(PARTITION  BY (1) ))AS numeric(10,2)),0) as Per

from cte2
group by Type,Weight
union all

Select 'Total', sum(isnull(cast(Weight as float),0)),sum(ISNULL(cast((Weight)*100.0/(sum(Weight)over(PARTITION  BY (1) ))AS numeric(10,2)),0))   from cte2
group by Weight
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,225 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,513 questions
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-11-23T01:36:03.34+00:00

    Please try:

      with cte as(
      SELECT   C.ContNo,
      --C.Rag_Type,
    
         case when cat.Cat_name='Savers' then 'A' else r.R_type_name END Rag_Type,
    
      C.entrydate as EntryDate, C.ConWeight as Act_weight,  C.No_Of_Bales as Act_Qty
      ,iSNULL(Sum(I.R_Weight),0)Isu_Weight
      ,iSNULL(Sum(i.R_QTY),0)Isu_QTY,(C.ConWeight-iSNULL(Sum(I.R_Weight),0)) as Pending_Weight,
      (C.No_Of_Bales-iSNULL(Sum(i.R_QTY),0)) as Pending_Qty
      FROM     Containerno C
      inner join ConCatagory cat on cat.Cat_ID=c.Cat_ID
      inner   join tbl_ContD CD on CD.CID=C.CID
      left   join tbl_Issuance_Rags I on I.D_ID=CD.D_ID
      left join tbl_Range as r on (c.con_value >= r.R_Value_From AND C.con_value <= r.R_Value_to)
    
      where C.delid is null and I.Delid is null and C.entrydate>'2020-12-31'  and c.con_status is null
      group by  C.ContNo,C.Contrackno, C.ConWeight,C.No_Of_Bales,C.entrydate,cat.Cat_name,r.R_type_name
      ),
      cte2 as(
      select Rag_Type Type ,sum(Pending_Weight) Weight from cte
      where Pending_Qty>0 and EntryDate>'2020-12-31'
      group by Rag_Type)
    
      select Type,Weight,
      ISNULL(cast((Weight)*100.0/(sum(Weight)over(PARTITION  BY (1) ))AS numeric(10,2)),0) as Per
    
      from cte2
      group by Type,Weight
      union all
      Select 'Total', sum(isnull(cast(Weight as float),0))
      ,sum(cc)   
      from (select *,ISNULL(cast((Weight)*100.0/(sum(Weight)over(PARTITION  BY (1) ))AS numeric(10,2)),0) cc
            from cte2) c2
      --group by Weight
    

    If this does not solve your problem, please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …).

    Regards,
    Echo

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-11-22T02:03:35.507+00:00

    Hi @Analyst_SQL ,

    As mentioned in error message, you cannot use sum()over() aggregate window function inside sum aggregate.

    Try using a derived table:

     with cte as(  
     SELECT   C.ContNo,  
     --C.Rag_Type,  
          
        case when cat.Cat_name='Savers' then 'A' else r.R_type_name END Rag_Type,  
          
     C.entrydate as EntryDate, C.ConWeight as Act_weight,  C.No_Of_Bales as Act_Qty  
     ,iSNULL(Sum(I.R_Weight),0)Isu_Weight  
     ,iSNULL(Sum(i.R_QTY),0)Isu_QTY,(C.ConWeight-iSNULL(Sum(I.R_Weight),0)) as Pending_Weight,  
     (C.No_Of_Bales-iSNULL(Sum(i.R_QTY),0)) as Pending_Qty  
     FROM     Containerno C  
     inner join ConCatagory cat on cat.Cat_ID=c.Cat_ID  
     inner   join tbl_ContD CD on CD.CID=C.CID  
     left   join tbl_Issuance_Rags I on I.D_ID=CD.D_ID  
     left join tbl_Range as r on (c.con_value >= r.R_Value_From AND C.con_value <= r.R_Value_to)  
            
     where C.delid is null and I.Delid is null and C.entrydate>'2020-12-31'  and c.con_status is null  
     group by  C.ContNo,C.Contrackno, C.ConWeight,C.No_Of_Bales,C.entrydate,cat.Cat_name,r.R_type_name  
     ),  
     cte2 as(  
     select Rag_Type Type ,sum(Pending_Weight) Weight from cte  
     where Pending_Qty>0 and EntryDate>'2020-12-31'  
     group by Rag_Type)  
          
     select Type,Weight,  
     ISNULL(cast((Weight)*100.0/(sum(Weight)over(PARTITION  BY (1) ))AS numeric(10,2)),0) as Per  
          
     from cte2  
     group by Type,Weight  
     union all  
     Select 'Total', sum(isnull(cast(Weight as float),0))  
     ,sum(cc)     
     from (select *,ISNULL(cast((Weight)*100.0/(sum(Weight)over(PARTITION  BY (1) ))AS numeric(10,2)),0) cc  
           from cte2) c2  
     group by Weight  
    

    Regards,
    Echo


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".