Need help with SQL SELECT string

[jdoneth]

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

[timothybard]

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.

[jdoneth]

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"

[timothybard]

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.