Grouping and sub Total in data table C# ASP.NET

Question

I have a DATA Table :

                COLUMN:  City    Deposittype  DepositAmount                  
                ROWS :  city1     new          100
                        city1     new          200
                        city2     old          200
                        city2     old          100
                        city2     new          200
                         city3    new          100

Want to Group by city, calc sum of depositamount for specified Deposittype.

Example, for condition depositType= new

i want a row like

               city1 city2 city3
                300   200   100

I want sum of DepositAmounts Grouped by City with specific Deposit type. i.e Result row should have city1 city2 city3 as column names, under which sum of 'Depositamounts' for a specified loan type say Deposittype = new.

it has to be done in C# only. Im trying to do with LINQ. First time — Thejo Arjun, Oct 20 '12 at 11:13

score 0 · Answer 1 · answered Oct 20 '12 at 11:01

0

SELECT City, SUM(DepositAmount) FROM myTable
WHERE Deposittype = 'new'
GROUP BY City

answered Oct 20 '12 at 11:01

Petar Ivanov

91,536
11
82
95

He need the result in one row. – Yaqub Ahmad Oct 20 '12 at 11:03
Need the result in that format( ROW ) for a few specific Deposit types. And finally i will merge all those rows into a table. which is my final result. – Thejo Arjun Oct 20 '12 at 11:11

score 0 · Answer 2 · answered Oct 20 '12 at 11:01

0

SELECT  
   (
    SELECT SUM(DepositAmount) FROM myTable 
    WHERE Deposittype = 'New' AND City = 'City1'
   ) AS City1,
   (
    SELECT SUM(DepositAmount) FROM myTable 
    WHERE Deposittype = 'New' AND City = 'City2'
   ) AS City2,
   (
    SELECT SUM(DepositAmount) FROM myTable 
    WHERE Deposittype = 'New' AND City = 'City3'
   ) AS City3

FROM    myTable

answered Oct 20 '12 at 11:01

Yaqub Ahmad

27,569
23
102
149

I have to do this in c# only. either using LINQ or querying table. – Thejo Arjun Oct 20 '12 at 11:07
This is a dynamically created table. Thats why – Thejo Arjun Oct 20 '12 at 11:12

Grouping and sub Total in data table C# ASP.NET

2 Answers2