I have been using the below SELECT string for some time and it has worked well, I now need to add a SUM and GROUP BY to this string and have not been able to get it to work.

Dim selstrHISTORY As String = "SELECT history.route_no,history.Acct_no,history.Post_date,history.tran_code," & _
"history.Amount,SUM(history.Amount),SERVICE.parcel FROM history  INNER JOIN SERVICE ON service.serv_id = history.serv_id " & _
" WHERE history.route_no = 'PEN' AND history.post_date = " & "{" & DataGridViewDates.CurrentCell.Value.ToString & "}" & _
"AND history.tran_code = 'AC' GROUP BY history.Acct_no"

Need to add:

SUM(history.Amount)
GROUP BY history.Acc_no

Thanks for any help
Joe

Edited 3 Years Ago by happygeek: fixed formatting

When you make the query a Group By query, I believe you need to add that you are grouping by all the other fields as well, not just history.Acct_no.

Dim selstrHISTORY As String = "SELECT history.route_no,history.Acct_no,history.Post_date,history.tran_code," & _
"history.Amount,SUM(history.Amount),SERVICE.parcel FROM history INNER JOIN SERVICE ON service.serv_id = history.serv_id " & _
" WHERE history.route_no = 'PEN' AND history.post_date = " & "{" & DataGridViewDates.CurrentCell.Value.ToString & "}" & _
"AND history.tran_code = 'AC' GROUP BY history.route_no, history.Acct_no, history.Post_date, history.tran_code, history.Amount, SERVICE.parcel"

If you don't want to group with the other fields, then either don't include them in the query or SUM them or perform some other calculation on them.

Problem solved thanks to help from timothybard

Tim you pointed me in the right direction, actual string ends up as follows.
Thanks

Dim selstrHISTORY As String = "SELECT history.route_no,history.Acct_no,history.Post_date,history.tran_code," & _
"SUM(history.Amount),SERVICE.parcel FROM history INNER JOIN SERVICE ON service.serv_id = history.serv_id " & _
" WHERE history.route_no = 'PEN' AND history.post_date = " & "{" & DataGridViewDates.CurrentCell.Value.ToString & "}" & _
"AND history.tran_code = 'AC' GROUP BY history.route_no, history.Acct_no, history.Post_date, history.tran_code, SERVICE.parcel"

Edited 3 Years Ago by happygeek: fixed formatting

Ahh.. yes.. I noticed that you had history.amount and SUM(history.amount) in your original post, somewhere along the line, I had forgotten about it.

This question has already been answered. Start a new discussion instead.