Group by statement

Question

is there a way in sql someone can be able to group by if e.g student id ,student _name,student _age, select table student groupby by student age and then count the of student with 5 years and show if the total is 3

Carlos kambui · Answer

using the codes below i want to update column trans_quantity in the database but am unable to so kinldy correct me Public Sub savetriptrans() Dim tLocationStock As New LocationStockType Dim transtrip As New TripTransactiontype Dim ttcustomer As List(Of CustomerType) Dim ttrip As New Triptype Dim tCustomer As CustomerType = svcClient.GetCustomer(MdlConn.MainConn MdlConn.SerchID) For Each row As DataRow In Itemtable.Rows tLocationStock = svcClient.GetLocationStock(MdlConn.checkDB row(StockId)) Dim sTran As New TransactionFileType 'svcClient.GetCustomer(MdlConn.checkDB tReceipt.Customer_id) ttcustomer = svcClient.GetCustomers(MdlConn.checkDB MdlConn.branchid) svcClient.Gettrip(MdlConn.checkDB tReceipt.route_id Now.Date) transtrip.trip_date = Now transtrip.trans_quantity = transtrip.trans_quantity + row(qty) transtrip.location_Productcode = row(StockId) transtrip.route_id = tCustomer.route_id transtrip.customer_id = tCustomer.Customer_id transtrip.location_product_description = tLocationStock.location_product_description svcClient.Settriptransaction(MdlConn.checkDB transtrip Convert.ToInt32(row(qty)) Convert.ToInt32(row(StockId))) Next End Sub below is my sql statement Public Function Settriptransaction(ByVal ConnString As String ByVal ttrip As TripTransactiontype ByVal qnty As Integer ByVal Productcode As Integer) As ReturnType Dim result As New ReturnType If ttrip Is Nothing Then Throw New ArgumentNullException(Trip is null) Else Using conn As New SqlConnection(ConnString) Using Command As SqlCommand = conn.CreateCommand() conn.Open() If ttrip.trip_transaction_id = 0 Then 'Command.CommandText =  update trip_transactions set trans_quantity=' & qnty & ' where location_Productcode=' & Productcode & ' Command.CommandText = INSERT INTO trip_transactions (route_trip_idroute_idcustomer_idlocation_Productcodelocation_product_descriptiontrans_quantitytrip_date) VALUES (@route_trip_id@route_id@customer_id@location_Productcode@location_product_description@trans_quantity@trip_date) SET @trip_transaction_id = (SELECT SCOPE_IDENTITY()); Else Command.CommandText = UPDATE trip_transactions set route_trip_id=@route_trip_idroute_id=@route_idcustomer_id=@customer_idlocation_Productcode=@location_Productcodelocation_product_description=@location_product_descriptiontrans_quantity=' & qnty & 'trip_date=@trip_date WHERE trip_transaction_id=trip_transaction_id 'Command.CommandText =  update trip_transactions set trans_quantity=' & qnty & ' where location_Productcode=' & Productcode & ' 'Command.CommandText = INSERT INTO trip_transactions (route_trip_idroute_idcustomer_idlocation_Productcodelocation_product_descriptiontrans_quantitytrip_date) VALUES (@route_trip_id@route_id@customer_id@location_Productcode@location_product_description@trans_quantity@trip_date) SET @trip_transaction_id = (SELECT SCOPE_IDENTITY()); End If Dim objParameter As New SqlClient.SqlParameter(@trip_transaction_id SqlDbType.Int) objParameter.Direction = ParameterDirection.InputOutput objParameter.Value = ttrip.route_trip_id Command.Parameters.Add(objParameter) Command.Parameters.Add(@route_trip_id SqlDbType.Int ParameterDirection.Input).Value = ttrip.route_trip_id Command.Parameters.Add(@route_id SqlDbType.Int ParameterDirection.Input).Value = ttrip.route_id Command.Parameters.Add(@customer_id SqlDbType.Int ParameterDirection.Input).Value = ttrip.customer_id Command.Parameters.Add(@location_Productcode SqlDbType.Int ParameterDirection.Input).Value = ttrip.location_Productcode Command.Parameters.Add(@location_product_description SqlDbType.VarChar 225 ParameterDirection.Input).Value = ttrip.location_product_description Command.Parameters.Add(@trans_quantity SqlDbType.Int ParameterDirection.Input).Value = ttrip.trans_quantity Command.Parameters.Add(@trip_date SqlDbType.Date ParameterDirection.Input).Value = Now.Date 'Try result.return_code = Command.ExecuteNonQuery() If result.return_code = 1 Then result.return_identity_code = Command.Parameters(@trip_transaction_id).Value result.return_message = Trip Process succesfull Else End If 'Catch ex As Exception ' result.return_message = PatientRegistration Status already exists 'End Try End Using End Using End If Return result End Function am able to insert but not to update  i want to update column trans_quantity

Suvendu Shekhar Giri · Answer

Would like to request to explain the actual requirement instead of suggesting to use sql keywords. We'll try to suggest you the optimum way to solve your problem. However looking at your requirement something like following can be used to determine if you have more than 3 students with age 5- SELECT COUNT(*) FROM student WHERE student_age=5 Please let me know if your requirement is something else. Thanks