John John - 5 months ago 10
SQL Question

C#: Select statement does not appear the right result

I created function analyze students result table. The aim is : Find the highest average result which grouped by Content type. For Example:

S_Id ContentType Result
1 T 50
1 V 70
1 G 30
1 G 40
1 V 60


The output that i need it is V because the average of V is the highest.

This is my Code but it does not display the right result:
My output display the fifth row in the table.

public string analyzeResultTable(string studentId)
{
string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["AHSConnection"].ToString();
DataSet ds = new DataSet();
DataSet dsAns = new DataSet();
string BestPrefrence = "";
using (MySqlConnection conn = new MySqlConnection(connStr))
{
conn.Open();
MySqlCommand cmd2 =
new MySqlCommand(
"Select ContentType, MAX(avgContent) from (select ContentType, AVG(result) as avgContent from dopractice where S_Id='" +
studentId + "' GROUP BY ContentType) AS T", conn);
MySqlDataAdapter da = new MySqlDataAdapter(cmd2);
da.Fill(ds);
conn.Close();
}

if (ds.Tables[0].Rows.Count > 0)
{
BestPrefrence = ds.Tables[0].Rows[0]["ContentType"].ToString();
}
return BestPrefrence;
}

Answer

this wil give you the average result for each ContentType

select ContentType, AVG(result) as avgContent from dopractice where S_Id='" + studentId + "' GROUP BY ContentType order by AVG(result) desc limit 1;

after adding order by you will get what you want