title: Select SUM a column and group by
body: <p>I have 2 tables (tblGenerator AND tblGeneratorLine). tblGenerator contains ingormation about each generator, and the tblGeneratorLine contains the rent per month per generator. I want to get a list of total RentPayd per generator in one year. I am using the code below, when I run the code I get this
  error:</p>
  
  <blockquote>
    <p>In aggregate and grouping expressions, the SELECT clause can contain
    only aggregates and grouping expressions. [ Select clause = g,Zoon ]</p>
  </blockquote>
  
  <p>Can any one please help.</p>
  
  <pre><code>internal static DataTable getRentByYear(int year)
  {
      string sql = "Select SUM(l.RentPayd) AS Payd, l.GenId, g.Zoon from tblGeneratorLine AS l INNER JOIN tblGenerator AS g on l.GenId=g.GenId where l.Year=@year Group By l.GenId Order By l.GenId";
  
      if (con.State == ConnectionState.Closed)
          con.Open();
      DataTable dt = new DataTable();
      try
      {
          SqlCeDataAdapter adp = new SqlCeDataAdapter();
          adp = new SqlCeDataAdapter(sql, con);
          adp.SelectCommand.Parameters.Add("@year", SqlDbType.Int).Value = year;
           adp.Fill(dt);
      }
      catch (Exception ex)
      {
          throw ex;
      }
      finally
      {
          con.Close();
      }
      return dt;
  }
  </code></pre>
  
  <p>Where the extracted SQL query is:</p>
  
  <pre><code>select SUM(l.RentPayd) AS Payd, l.GenId, g.Zoon
  from tblGeneratorLine AS l
      INNER JOIN tblGenerator AS g on l.GenId = g.GenId
  where l.Year = @year
  group By l.GenId
  order By l.GenId
  </code></pre>