Need help with SQL SELECT string
 

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

Re: Need help with SQL SELECT string
 

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.

Re: Need help with SQL SELECT string
 

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"

Re: Need help with SQL SELECT string
 

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.