Want to add the rows data

Anurag Srivastava

15y2.4k

Hi

I have a problem, want to add row data and GROUP the data by using SrNo
and Months

         SrNo        Months        Value
          1           Jan-2010        2
          1           Jan-2010        5
          2           Feb-2010        1
          2           Feb-2010        6
          2           Feb-2010        2
          3           Mar-2010        7
          3         Mar-2010        4

The result set should be like this:

        SrNo       Months         Value
         1          Jan-2010         7       (Sum of Jan-2010 value)
         2          Feb-2010        16     (Jan-2010 + Feb-2010)
         3          Mar-2010        27     (Jan-2010 + Feb-2010 + Mar-2010)

Help on urgent basis.
Thanks in advance
Regards
Anurag

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Syed Nayab Ali15y ago
Hi Anurag

Try to use following concept
It will give you proper result
I also post this answer in your previous question

SELECT a.SrNo as Srno ,a.Months as Months, SUM(a.value) as Value into #temp FROM Tablename a WITH(NOLOCK)
GROUP BY a.SrNo, a.Months

Select aaa.srno, aaa.Months, SUM(b.total) as value from #temp aaa join #temp bbb on bbb.srno<=aaa.srno
group by aaa.srno, aaa.Months

Thanks
Please click on "Accept Answer" if it helps you
0
Jean Paul15y ago
Hello Anurag,

I think the answer is wrong. it should be 1, 7 2, 9 3, 11
Based on the SerialNumber grouping I have create a quick lambda expression.
Please check it.

public class Item
{
public int SerialNumber;
public string Month;
public int Value;
}

IList list = new List();
list.Add(new Item() { SerialNumber = 1, Month = "Jan-2010", Value = 2 });
list.Add(new Item() { SerialNumber = 1, Month = "Jan-2010", Value = 5 });
list.Add(new Item() { SerialNumber = 2, Month = "Feb-2010", Value = 1 });
list.Add(new Item() { SerialNumber = 2, Month = "Feb-2010", Value = 6 });
list.Add(new Item() { SerialNumber = 2, Month = "Feb-2010", Value = 2 });
list.Add(new Item() { SerialNumber = 3, Month = "Mar-2010", Value = 7 });
list.Add(new Item() { SerialNumber = 3, Month = "Mar-2010", Value = 4 });

IList groupedList = list.GroupBy(i => i.SerialNumber).Select(g => new Item() {SerialNumber=g.Key,Month=g.First().Month, Value=g.Sum(gr => gr.Value) }).ToList();
0