How do I get the sum of the data in a datagridview by date. I'm doing a project which need to get the total sales from a specific date like to 5/13/2020 from 5/19/2020. I just need to get the total sum of sale from those days which is already in the table. thank you :).
this one lacks filtering of date range that I want
conns.Open();
DataTable dt = new DataTable();
adapt = new SqlDataAdapter("select * from tblSales", conns);
adapt.Fill(dt);
sale.DataSource = dt;
conns.Close();
int sum = Convert.ToInt32(dt.Compute("SUM(Total)", string.Empty));
income.Text = Convert.ToString(sum);
Should you use Linq Expression to WhereClause to filter by dates and Sum to get the total.
Sample code: //To simulate your DateTimePicker DateTimePicker dateTimePickerInit = new DateTimePicker(); dateTimePickerInit.Value = new DateTime(2020, 03, 01);
DateTimePicker dateTimePickerEnd = new DateTimePicker();
dateTimePickerEnd.Value = DateTime.Now.Date;
//To simulate your DataTable
DataTable dt = new DataTable("CompanySales");
dt.Columns.Add("Company", typeof(string));
dt.Columns.Add("Date", typeof(DateTime));
dt.Columns.Add("Total", typeof(decimal));
//Sample of rows
dt.Rows.Add("Company0", new DateTime(2020, 02, 01), 3000.2);
dt.Rows.Add("Company1", new DateTime(2020, 03, 01), 4000.2);
dt.Rows.Add("Company2", new DateTime(2020, 04, 01), 6000.2);
dt.Rows.Add("Company3", new DateTime(2020, 05, 01), 7000.2);
//The code of Linq expression WhereClause to filter and apply the results to another Datatable
var dtFiltered = dt.AsEnumerable()
.Where(x => x.Field<DateTime>("Date").Date >= dateTimePickerInit.Value && x.Field<DateTime>("Date").Date <= dateTimePickerEnd.Value)
.CopyToDataTable();
//The code of Linq expression to Apply Sum Total of the dtFiltered
var total = dtFiltered.AsEnumerable()
.Sum(x=> x.Field<decimal>("Total"));
Console.WriteLine(total);