title: Calculate rows per id and per period Month and Year
body: <p>I want to show a DataGridView with Compte Name and total sum of Recette and Depense per month and year.
  I have a DateTimePicker customized with month an year.<a href="http://s27.postimg.org/pi0wjbcsz/tutu.png" rel="nofollow">Image of the Winforms and the error</a></p>
  
  <p>Example : if I select date = 11/2015 (November 2015), it will show like that</p>
  
  <pre><code>Compte  Total   
  BTK     633,000
  Biat    987,888
  Caisse  988,875
  </code></pre>
  
  <p>and if I select date = 06/2016 (Jun 2016), it will show like that</p>
  
  <pre><code>Compte  Total   
  BTK     0
  Biat    653,256
  Caisse  88,145
  </code></pre>
  
  <p><strong>My Code :</strong></p>
  
  <pre><code>public partial class ComptesMois : Form
  {
      SqlConnection conn;
      SqlCommand comm;
      SqlDataReader dreader;
      string connstring = "Data Source=.;Initial Catalog=Tresorerie;Integrated Security=True";
      public ComptesMois()
      {
          InitializeComponent();
          //Set Columns Count
          DataGridViewCompteMois.ColumnCount = 2;
  
          //Hide the last blank line
          DataGridViewCompteMois.AllowUserToAddRows = false;
  
          //Add Columns
          DataGridViewCompteMois.Columns[0].Name = "NomCom";
          DataGridViewCompteMois.Columns[0].HeaderText = "Nom de Compte";
          DataGridViewCompteMois.Columns[0].DataPropertyName = "NomCom";
          DataGridViewCompteMois.Columns[0].Width = 100;
  
          //Add Columns
          DataGridViewCompteMois.Columns[1].Name = "Total";
          DataGridViewCompteMois.Columns[1].HeaderText = "Total";
          DataGridViewCompteMois.Columns[1].DataPropertyName = "Total";
          DataGridViewCompteMois.Columns[1].Width = 100;
  
          this.BindGrid();
  
          CompteMoisdateTimePicker.Format = DateTimePickerFormat.Custom;
          CompteMoisdateTimePicker.CustomFormat = "MM yyyy";
          //CompteMoisdateTimePicker.ShowUpDown = true; // to prevent the calendar from being displayed
  
      }
  
      private void BindGrid()
      {
  
          DataGridViewCompteMois.DataSource = null;
          using (SqlConnection con = new SqlConnection(connstring))
          {
              using (SqlCommand cmd = new SqlCommand("select DISTINCT c.NomCom from Tresorerie t join Compte c on t.IdCom=c.IdCom", con))
              {
                  cmd.CommandType = CommandType.Text;
                  using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                  {
                      using (DataTable dt = new DataTable())
                      {
                          sda.Fill(dt);
                          DataGridViewCompteMois.DataSource = dt;
                      }
                  }
              }
          }
      }
  
  
      private void DataGridViewCompteMois_CellContentClick(object sender, DataGridViewCellEventArgs e)
      {
          string month = CompteMoisdateTimePicker.Value.Month.ToString();
          string year = CompteMoisdateTimePicker.Value.Year.ToString();
          //select * from &lt; table &gt; where month(searchDate) = Month_from_box and Year(searchDate) = Year_from_box
          // select sum(recette + depence) from Tresorerie where 
          String total;
  
          for (int i = 0; i &lt; DataGridViewCompteMois.Rows.Count; i++)
          {
              conn = new SqlConnection(connstring);
              conn.Open();
              String NomCompteDataGrid = Convert.ToString(DataGridViewCompteMois.Rows[i].Cells[0].Value);
              String IdCompteValue = "select IdCom from Compte where NomCom='" + NomCompteDataGrid + "'";
              comm = new SqlCommand(IdCompteValue, conn);
              int IdComValue = Convert.ToInt32(comm.ExecuteScalar());
              conn.Close();
  
              using (SqlConnection con = new SqlConnection(connstring))
              {
                  using (SqlCommand cmd = new SqlCommand("select sum(Recette + Depense) as Total from Tresorerie where IdCom=@IdCom GROUP BY Recette,Depense", con))
                  {
                      cmd.CommandType = CommandType.Text;
                      cmd.Parameters.AddWithValue("@IdCom", IdComValue);
                      con.Open();
                      total = Convert.ToString(cmd.ExecuteNonQuery());
                      con.Close();
                  }
              }
              DataGridViewCompteMois.Rows[i].Cells[1].Value = total;
          }
      }
  }
  </code></pre>
  
  <p>but there is an error, the result show me -1 for every Compte</p>
  
  <p>Update 1: Thanks to @Soner_Gönül, I modify the query statement, and I tested it on sql and it works, but the problem when I add the result to the grid cell, it shows empty, but I try to show it with messgae box and it works, my code :</p>
  
  <pre><code> String total;
      private void CompteMoisdateTimePicker_ValueChanged(object sender, EventArgs e)
      {
          string month = CompteMoisdateTimePicker.Value.Month.ToString();
          string year = CompteMoisdateTimePicker.Value.Year.ToString();
  
  
          for (int i = 0; i &lt; DataGridViewCompteMois.Rows.Count; i++)
          {
              conn = new SqlConnection(connstring);
              conn.Open();
              String NomCompteDataGrid = Convert.ToString(DataGridViewCompteMois.Rows[i].Cells[0].Value);
              String IdCompteValue = "select IdCom from Compte where NomCom='" + NomCompteDataGrid + "'";
              comm = new SqlCommand(IdCompteValue, conn);
              int IdComValue = Convert.ToInt32(comm.ExecuteScalar());
              conn.Close();
  
              using (SqlConnection con = new SqlConnection(connstring))
              {
                  using (SqlCommand cmd = new SqlCommand("select SUM( Depense + Recette ) from Tresorerie where IdCom=@IdCom and MONTH(DateTre) = @MONTH AND YEAR(DateTre) = @YEAR GROUP BY IdCom", con))
                  {
                      cmd.CommandType = CommandType.Text;
                      cmd.Parameters.AddWithValue("@IdCom", IdComValue);
                      cmd.Parameters.AddWithValue("@MONTH", month);
                      cmd.Parameters.AddWithValue("@YEAR", year);
                      con.Open();
                      total = Convert.ToString(cmd.ExecuteScalar());
                      con.Close();
                  }
              }
              DataGridViewCompteMois.Rows[i].Cells["Total"].Value = total;
              //MessageBox.Show(total.ToString());
          }
      }
  </code></pre>
  
  <p><strong>Update Solution: look I have a problem which cell selected, this code works</strong></p>
  
  <pre><code>private void CompteMoisdateTimePicker_ValueChanged(object sender, EventArgs e)
      {
          string month = CompteMoisdateTimePicker.Value.Month.ToString();
          string year = CompteMoisdateTimePicker.Value.Year.ToString();
          for (int i = 0; i &lt; DataGridViewCompteMois.Rows.Count; i++)
          {
              conn = new SqlConnection(connstring);
              conn.Open();
              String NomCompteDataGrid = Convert.ToString(DataGridViewCompteMois.Rows[i].Cells[1].Value);
              String IdCompteValue = "select IdCom from Compte where NomCom='" + NomCompteDataGrid + "'";
              comm = new SqlCommand(IdCompteValue, conn);
              int IdComValue = Convert.ToInt32(comm.ExecuteScalar());
              conn.Close();
  
              using (SqlConnection con = new SqlConnection(connstring))
              {
                  using (SqlCommand cmd = new SqlCommand("select SUM( Depense + Recette ) from Tresorerie where IdCom=@IdCom and MONTH(DateTre) = @MONTH AND YEAR(DateTre) = @YEAR GROUP BY IdCom", con))
                  {
                      cmd.CommandType = CommandType.Text;
                      cmd.Parameters.AddWithValue("@IdCom", IdComValue);
                      cmd.Parameters.AddWithValue("@MONTH", month);
                      cmd.Parameters.AddWithValue("@YEAR", year);
                      con.Open();
                      //MessageBox.Show(Convert.ToString(cmd.ExecuteScalar()));
                      DataGridViewCompteMois.Rows[i].Cells["Total"].Value = Convert.ToString(cmd.ExecuteScalar());
                      con.Close();
                  }
              }
          }
      }
  </code></pre>