SQL expression and ADO recordset

[bell]

Hello

I have the following SQL statements which also
adds (sum) fields .
the SQL seems to b fine but I keep getting errors when
i try to open the recordset.

strGroup = "Select ReportSummary.region, Sum(q1) As Q1, Sum(q2) As Q2, Sum(q3) As Q3, Sum(q4) As Q4, Sum(q5) As Q5, Sum(q6) As Q6, Sum(q7) As Q7, Sum(q8) As Q8, Sum(q9) As Q9, Sum(q10) As Q10, Sum(q11) As Q11,Sum(q12) as Q12, count(*) as ndNumber," _
& " Sum(q1r) As Q1R,Sum(q2r) As Q2R,Sum(q3r) As Q3R,Sum(q4r) As Q4R,Sum(q5r) As Q5R,Sum(q6r) As Q6R,Sum(q7r) As Q7R,Sum(q8r) As Q8R,Sum(q9r) As Q9R,Sum(q10r) As Q10R,Sum(q11r) As Q11R,Sum(q12r) As Q12R," _
& " (Q1+q1r) AS S1,(Q2+q2r) As S2, (Q3+q3r) As S3,(Q4+q4r) As S4,(Q5+q5r) As S5,(Q6+q6r) As S6,(Q7+q7r) As S7,(Q8+q8r) As S8,(Q9+q9r) As S9,(Q10+q10r) As S10,(Q11+q11r) As S11,(Q12+q12r) As S12" _
& " From ReportSummary GROUP BY ReportSummary.region HAVING ReportSummary.Region=" & "'" & Trim(lstRegion.List(lstRegion.ListIndex)) & "'"


Set groupRs = New ADODB.Recordset


groupRs.Open strGroup, myConnection, adOpenKeyset, adLockOptimistic

The message i am getting is

The expression Q1+Q2 is not included in the aggregate function

Thank u for your time.

[sridhar kumar]

ReportSummary.Region=" & "'" & Trim(lstRegion.List(lstRegion.ListIndex)) & "'"


hello,

in your query the in last line remove " after = and keep only & "'" & Trim(lstRegion.List(lstRegion.ListIndex)) & "'" and try ,... hope it may work

[bell]

No that does not do the job

[vb_dba]

The addition statements, ie) (Q1 + Q1r), are not aggregated statements, in other words, they don't sum, count, avg, etc. They are treated a data from a single row in the table. As such, they need to be included in the Group By statement:

strGroup = "Select ReportSummary.region, " & _
           "       Sum(q1) As Q1, Sum(q2) As Q2, Sum(q3) As Q3, " & _
           "       Sum(q4) As Q4, Sum(q5) As Q5, Sum(q6) As Q6, " & _
           "       Sum(q7) As Q7, Sum(q8) As Q8, Sum(q9) As Q9, " & _
           "       Sum(q10) As Q10, Sum(q11) As Q11, Sum(q12) as Q12, " & _
           "       count(*) as ndNumber, " & _
           "       Sum(q1r) As Q1R, Sum(q2r) As Q2R, Sum(q3r) As Q3R, " & _
           "       Sum(q4r) As Q4R, Sum(q5r) As Q5R, Sum(q6r) As Q6R, " & _
           "       Sum(q7r) As Q7R, Sum(q8r) As Q8R, Sum(q9r) As Q9R, " & _
           "       Sum(q10r) As Q10R, Sum(q11r) As Q11R, Sum(q12r) As Q12R, " & _
           "       (Q1+q1r) As S1, (Q2+q2r) As S2,  (Q3+q3r) As S3, " & _
           "       (Q4+q4r) As S4, (Q5+q5r) As S5, (Q6+q6r) As S6, " & _
           "       (Q7+q7r) As S7, (Q8+q8r) As S8, (Q9+q9r) As S9, " & _
           "       (Q10+q10r) As S10, (Q11+q11r) As S11, (Q12+q12r) As S12 " & _
           "From ReportSummary " & _
           "GROUP BY ReportSummary.region[b], (Q1+q1r), (Q2+q2r), (Q3+q3r), (Q4+q4r), (Q5+q5r), " & _
           "(Q6+q6r), (Q7+q7r), (Q8+q8r), (Q9+q9r), (Q10+q10r), (Q11+q11r), (Q12+q12r)[/b] " & _
           "HAVING ReportSummary.Region='" & Trim(lstRegion.List(lstRegion.ListIndex)) & "'"