-1

I'm getting an error

Column invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Actually my code works in mysql database but I am using SQL Server 2008 R2 now. Please help me

This is my code: 

Class1.sqlStatement = "Select Max(tix), dealer, module, uname, dept, prob, pic, recieved, dresolved, stamp, tresoved, aging, typee, status, assigned, stat, root, remarks, deploystat, reply from tblhd Group By tix HAVING MAX(tix) > 19999";

Class1.dbcommand = new SqlCommand(Class1.sqlStatement, Class1.dbconnection);

            Class1.reader = Class1.dbcommand.ExecuteReader();
            while (Class1.reader.Read())
            {
                lstitem = listView1.Items.Add(Class1.reader[0].ToString());
                lstitem.SubItems.Add(Class1.reader[1].ToString());
                lstitem.SubItems.Add(Class1.reader[2].ToString());
                lstitem.SubItems.Add(Class1.reader[3].ToString());
                lstitem.SubItems.Add(Class1.reader[4].ToString());
                lstitem.SubItems.Add(Class1.reader[5].ToString());
                lstitem.SubItems.Add(Class1.reader[6].ToString());
                lstitem.SubItems.Add(Class1.reader[7].ToString());
                lstitem.SubItems.Add(Class1.reader[8].ToString());
                lstitem.SubItems.Add(Class1.reader[9].ToString());
                lstitem.SubItems.Add(Class1.reader[10].ToString());
                lstitem.SubItems.Add(Class1.reader[11].ToString());
                lstitem.SubItems.Add(Class1.reader[12].ToString());
                lstitem.SubItems.Add(Class1.reader[13].ToString());
                lstitem.SubItems.Add(Class1.reader[14].ToString());
                lstitem.SubItems.Add(Class1.reader[15].ToString());
                lstitem.SubItems.Add(Class1.reader[16].ToString());
                lstitem.SubItems.Add(Class1.reader[17].ToString());
                lstitem.SubItems.Add(Class1.reader[18].ToString());
                lstitem.SubItems.Add(Class1.reader[19].ToString());
            }
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jeric Canatuan
  • 11
  • 4
  • Add All the columns of Select except MAX(tix) in your GROUP BY Clause. I think that will solve your problem. – Paresh J May 15 '15 at 11:55
  • 2
    Really that exact code runs in mysql? I don't believe that. Lets start with dealer. Is dealer in either an aggregate function or the GROUP BY clause? – paparazzo May 15 '15 at 11:55
  • SQL (the language) doesn't allow this. It's not SQL Server. In fact, it doesn't make sense to mix aggregates and columns that aren't in the `group by` clause - what is `MAX(tix)` supposed to return? The overall MAX, or the max of a specific subset of columns? Which subset? – Panagiotis Kanavos May 15 '15 at 12:02

1 Answers1

0

In SQL server this is not allowed, since you're asking for fields that are not contained in the GROUP BY clause or in an aggregate function, like MAX. The only way is to put all of the requested fields in the GROUP BY clause

Simone
  • 1,828
  • 1
  • 13
  • 20