Marius Vasile

Marius Vasile

  • 590
  • 1.9k
  • 147.2k

select count of items for each month grouped by name

Sep 15 2024 5:05 AM

I have a table with the following structure ID  DATE  DEP_C that contains a lot of data.

I want to display in a Datalist for each month of the year a count of each DEP_C grouped by DEP_C

where A1.1, A1.2, A1.3 is DEP_C

I tried with

protected void GetData()
{
    int currentYear = DateTime.Now.Year;
    using (SqlConnection conn = new SqlConnection(connString))
    {
        string sqlQuery1 = "SELECT Dep_C, DATEPART(MONTH, Data) AS Month, Count(Dep_C) AS CountDef FROM RCA_Import WHERE DATEPART(YEAR, RCA_Import.Data)=@CurrentYear GROUP BY Month";
        using (SqlCommand cmd = new SqlCommand(sqlQuery1, conn))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@CurrentYear", currentYear);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            conn.Open();
            DataSet ds = new DataSet();
            da.Fill(ds);
            DataList1.DataSource = ds.Tables[0];
            DataList1.DataBind();
            conn.Close();
        }
    }
}

but luck. How it should be done, please?

 


Answers (1)