Display Sum of record for each group using C# and VB.Net in ASP.Net

Hi micah,

Use group by to sum based on Category.

Refer below query and using the query bind the GridView.

SQL

CREATE TABLE #Details
(
	[ID] int IDENTITY(1,1) NOT NULL,
	[Category] VARCHAR(50),
	[SubCategory] VARCHAR(50),
	[Price] DECIMAL(18,2),
	[Date] DateTime
)

INSERT INTO #Details VALUES ('Cloth','Female',3,'2019/03/04')
INSERT INTO #Details VALUES ('Car','Toyota',2000,'2019/03/04')
INSERT INTO #Details VALUES ('Cloth','Male',9,'2019/03/04')
INSERT INTO #Details VALUES ('Car','Honder',3000,'2019/03/04')
INSERT INTO #Details VALUES ('Cloth','Coporate wear',123,'2019/03/05')


SELECT Category,SUM(Price) AS Price,Date FROM #Details
GROUP BY Category,Date

DROP TABLE #Details

Check this example. Now please take its reference and correct your code.

HTML

<asp:GridView ID="gvCategories" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="Category" HeaderText="Category" />
        <asp:BoundField DataField="Price" HeaderText="Price" />
        <asp:BoundField DataField="Date" HeaderText="Date" />
    </Columns>
</asp:GridView>

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.BindGrid();
    }
}

private void BindGrid()
{
    string conString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT Category,SUM(Price) AS Price,CONVERT(VARCHAR(15),Date,103) AS Date FROM Details GROUP BY Category,Date", con))
        {
            cmd.CommandType = CommandType.Text;
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                sda.SelectCommand.CommandType = CommandType.Text;
                sda.Fill(dt);
                this.gvCategories.DataSource = dt;
                this.gvCategories.DataBind();
            }
        }

    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.BindGrid()
    End If
End Sub

Private Sub BindGrid()
    Dim strcon As String = ConfigurationManager.ConnectionStrings("ConString").ConnectionString
    Using con As SqlConnection = New SqlConnection(strcon)
        Using cmd As SqlCommand = New SqlCommand("SELECT Category,SUM(Price) AS Price,CONVERT(VARCHAR(15),Date,103) AS Date FROM Details GROUP BY Category,Date", con)
            cmd.CommandType = CommandType.Text
            Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                Dim dt As DataTable = New DataTable()
                sda.SelectCommand.CommandType = CommandType.Text
                sda.Fill(dt)
                Me.gvCategories.DataSource = dt
                Me.gvCategories.DataBind()
            End Using
        End Using
    End Using
End Sub

Output