Problem with query MySql + VBA

[ToommS]

Why after this query

rst.Open "SELECT @T1:=(SUM(IF(JOB IN('PM','BT') AND CMD='DT',0,RT))-SUM(IF(JOB NOT IN('PM','BT') AND CMD Regexp 'C(S|T)T(S|T)|LC|DT|PP',RT,0)))/SUM(IF(JOB IN('PM','BT') AND CMD='DT',0,RT)),@T1 from rfb", conn

is an error - 'Run Time Error - 2147217871
Mixing of Group columns (Min(),Max(),Count(),...) with no Group columns is illegal if there is no Group By clause'

 

[rac2]

Maybe this will work.

SELECT @T1 := (
                SUM(
                  IF(JOB IN('PM','BT') AND CMD='DT',0,RT)
                )
                - SUM(
                    IF(JOB NOT IN('PM','BT') AND CMD Regexp 'C(S|T)T(S|T)|LC|DT|PP',RT,0)
                  )
              )
              / SUM(
                  IF(JOB IN('PM','BT') AND CMD='DT',0,RT)
                )


from rfb


SELECT @T1

See the bolded Note

http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

In general, aggregate functions such as MIN(), MAX(), SUM() can be used in a SELECT query without the GROUP BY clause only if no other expressions or columns are in the SELECT list. So your statement might fail for that reason.

And the note from the manual says that @T1 could not be placed in a GROUP BY clause.