user1260572 user1260572 - 19 days ago 12
Vb.net Question

SQL Server grouping column value

I have am having a problem grouping the column of my table

Private Sub BindRepeater()
Dim cn As String = ConfigurationManager.ConnectionStrings("db").ConnectionString
Using cs As New SqlConnection(cn)
Using cmd As New SqlCommand("SELECT * from tbl_in_record GROUP BY in_pc_no", cs)
Using sda As New SqlDataAdapter(cmd)
Dim dt As New DataTable()
sda.Fill(dt)
rptTableIn.DataSource = dt
rptTableIn.DataBind()
End Using
End Using
End Using
End Sub


It displays this error


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


My table looks like this

in_id in_pc_no





1 101-33X-7104-01
2 101-33X-7104-01
3 101-33X-7104-01
12 101-33X-7014-02
13 101-33X-7014-02
14 37H-33Y-7066-01
15 37H-33Y-7066-01

Answer

So, you would use either:

SELECT DISTINCT in_pc_no FROM tbl_in_record;

or

SELECT in_pc_no FROM tbl_in_record GROUP BY in_pc_no;

Any other fields that are included need to have some aggregation function, e.g.

SELECT in_pc_no, n = Count(in_id) FROM tbl_in_record GROUP BY in_pc_no;