Calculate Row Wise Sum - SQL Server

How to sum column wise and row-wise in SQL Server?

I think you can use GROUP BY with GROUPING SETS

declare @t table(aggrementid varchar(20), bom_pos int, bucket int null, paymentstatus varchar(50))

insert into @t 
    select '1', 3000, null, null  
    union all 
    select '2', 3000, 0, 'Non-Delinquient' 
    union all
    select '3', 4000, 0, 'Non-Delinquient' 
    union all 
    select '4', 5000, 0, 'Non-Delinquient' 
    union all 
    select '5', 7000, 0, 'NPA' 
    union all 
    select '6', 8000, 1, 'NPA'

Select 
    isnull(CAST(bucket AS varchar(10)),'Total') bucket,
    SUM([Non-Delinquient]) [Non-Delinquient],
    SUM([NPA]) [NPA],
    SUM([RollBack]) [RollBack],
    SUM([RollForward]) [RollForward],
    SUM([Stabilized]) [Stabilized],
    SUM([Normalized]) [Normalized],
    SUM([PaymentStatusY]) [PaymentStatusY],
    SUM(isnull([Non-Delinquient],0) + isnull([NPA],0) + isnull([RollBack],0) + isnull([RollForward],0) + isnull([Stabilized],0) + isnull([Normalized],0) + isnull([PaymentStatusY],0)) Total
From
    (Select 
         --aggrementid, 
         bom_pos,
         bucket, paymentstatus      
     From 
         @t) as PivotSource
PIVOT
    (sum(bom_pos) FOR paymentstatus IN ([Non-Delinquient], [NPA],[RollBack],[RollForward],[Stabilized],[Normalized],[PaymentStatusY])
    ) as Pvt
Where 
    bucket is not null
GROUP BY GROUPING SETS(
                        (bucket),
                        ()
                      )

See also the following example for understanding how it works

SELECT
  CASE GROUPING_ID(GroupID,SubgroupID)
    WHEN 3 THEN 'Total'
    WHEN 1 THEN 'Subtotal by Group'
    WHEN 0 THEN ''
  END RowTitle,

  GroupID,
  SubgroupID,
  SUM(Value) Value
FROM
  (
    SELECT 1 GroupID,1 SubgroupID,10 Value
    UNION ALL SELECT 1 GroupID,2 SubgroupID,5 Value
    UNION ALL SELECT 1 GroupID,3 SubgroupID,5 Value
    UNION ALL SELECT 2 GroupID,1 SubgroupID,11 Value
    UNION ALL SELECT 2 GroupID,2 SubgroupID,12 Value
  ) q
GROUP BY GROUPING SETS(
            (GroupID,SubgroupID),
            (GroupID),
            ()
          )

You also can use SUM with CASE instead PIVOT. For me it's more clearly

SELECT
  bucket,
  SUM(CASE WHEN paymentstatus='Non-Delinquient' THEN bom_pos END) [Non-Delinquient],
  SUM(CASE WHEN paymentstatus='NPA' THEN bom_pos END) [NPA],
  SUM(CASE WHEN paymentstatus='RollBack' THEN bom_pos END) [RollBack],
  SUM(CASE WHEN paymentstatus='RollForward' THEN bom_pos END) [RollForward],
  SUM(CASE WHEN paymentstatus='Stabilized' THEN bom_pos END) [Stabilized],
  SUM(CASE WHEN paymentstatus='Normalized' THEN bom_pos END) [Normalized],
  SUM(CASE WHEN paymentstatus='PaymentStatusY' THEN bom_pos END) [PaymentStatusY],
  SUM(bom_pos) Total
FROM @t
WHERE paymentstatus IN('Non-Delinquient', 'NPA','RollBack','RollForward','Stabilized','Normalized','PaymentStatusY')
GROUP BY GROUPING SETS(
                        (bucket),
                        ()
                      )

SQL Row wise total value

The SUM() function is an aggregate function. As with other aggregates, use it only to compute values across multiple rows.

You want to add up values in one row, so just use the + operator (brackets are optional).

As for finding the minimum value in the row, use CASE WHEN with 3 tests, comparing S1, S2, S3 and S4.

This should work:

select 
  c.id, c.date, c.name, c.s1, c.s2, c.s3, c.s4, 
  (c.s1 + c.s2 + c.s3 + c.s4) as total,
  case 
    when c.s1 <= c.s2 and c.s1 <= c.s3 and c.s1 <= c.s4 then c.s1
    when c.s2 <= c.s1 and c.s2 <= c.s3 and c.s2 <= c.s4 then c.s2
    when c.s3 <= c.s2 and c.s3 <= c.s1 and c.s3 <= c.s4 then c.s3
    when c.s4 <= c.s2 and c.s4 <= c.s3 and c.s4 <= c.s1 then c.s4
  end as min_value
from calcu c
;

See SQLFiddle

Sql get sum row by row

You seem to want a cumulative sum. This is supported as:

select t.*,
       sum(price) over (order by id) as total_price
from t;

SQL Server has supported this ANSI-standard syntax since SQL Server 2012.

In earlier versions, you can use a correlated subquery:

select t.*,
       (select sum(t2.price)
        from t t2
        where t2.id <= t.id
       ) as total_price
from t;

EDIT:

As John suggests, if you want a separate sum for each pname, then the syntax would be:

select t.*,
       sum(t.price) over (partition by p.name order by t.id) as total_price
from t;

Pivot with row wise Sum in SQL Server

There are few things wrong in your query. First of all you selected column 'Slab' which is not in your table (might by due to copy from another query) Instead you need to select custno and name.

Then your query will run but you will have three rows for for each customer since each customer has three distinct value in quantity field. The reason behind is the group by clause (group by CustNo,[Name],BrandName,Qty) in inner query. Instead I have used window function to sum(amt) for each customer.

I have also used two set of dynamic column names to get rid of null value in the result. One to pivot as you used in your code (@cols) and other list contains coalesce(columnname,0) to convert null into 0.

And if you are using SQL Server 2017 and onward version then I would suggest to use string_agg() to concatenate the column names since it's easier and faster in performance. I have used it in Query#2.

Schema and insert statement:

create table [Table-A](PID int, BrandName varchar(50));
insert into [Table-A] values(1  ,'Brand1');
insert into [Table-A] values(2  ,'Brand2');
insert into [Table-A] values(3  ,'Brand3');
insert into [Table-A] values(4  ,'Brand4');
insert into [Table-A] values(5  ,'Brand5');
insert into [Table-A] values(6  ,'Brand6');
insert into [Table-A] values(7  ,'Brand7');
insert into [Table-A] values(8  ,'Brand8');

create table [TABLE-B]( CustNo  int,Name varchar(10),BrandName varchar(50),Qty int, Amt int);
insert into [TABLE-B] values(1  ,'C1',  'Brand1',   3,  300);
insert into [TABLE-B] values(1  ,'C1',  'Brand2',   2,  400);
insert into [TABLE-B] values(1  ,'C1',  'Brand4',   1,  300);
insert into [TABLE-B] values(1  ,'C1',  'Brand5',   2,  100);
insert into [TABLE-B] values(2  ,'C2',  'Brand1',   2,  200);
insert into [TABLE-B] values(2  ,'C2',  'Brand3',   1,  200);
insert into [TABLE-B] values(3  ,'C3',  'Brand2',   1,  300);
insert into [TABLE-B] values(3  ,'C3',  'Brand7',   3,  150);

Query#1 (using stuff() and xml path for())

DECLARE @cols AS NVARCHAR(MAX),
    @colsForSelect AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)
 
 
SET @colsForSelect = STUFF((SELECT  ',' + ' Coalesce('+quotename(BrandName)+',0) '+ quotename(BrandName)
            FROM [TABLE-A] order by pid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
        
select @cols = STUFF((SELECT ',' + QUOTENAME(BrandName) from [TABLE-A] order by PID FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

set @query = 'SELECT custno,name,' + @colsForSelect + ',Amt
             from 
             (
select CustNo,[Name],Qty,SUM(cast([amt] as float))over(partition by custno) as Amt,BrandName from [TABLE-B]             ) x
            pivot 
            (
                max(Qty)
                for brandname in (' + @cols + ')
            ) p '

execute(@query)

Output:

with dates as (
      select min(datefromparts(year(startdate), month(startdate), 1)) as dte,
             max(datefromparts(year(enddate), month(enddate), 1)) as enddte
      from tblVal
      union all
      select dateadd(month, 1, dte), enddte
      from dates
      where dte < enddte
     )
select d.dte, sum(val)
from dates d left join
     tblval t
     on t.startdate <= eomonth(dte) and
        t.enddate >= dte
group by d.dte
order by d.dte;