Complementing the post above: I have found that SUM(DISTINCT xxx) is valid with v.5x, not so in v.4x. While I am trying to convince my ISP to switch the database to a v.5x server, I would like some help with a workaround - please excuse my newbness. I have tried :
(select sum(#variables.tableprefix#threads.views) from #variables.tableprefix#threads where t#variables.tableprefix#hreads.forumidfk = #variables.tableprefix#forums.id) as views, instead of : sum(distinct #variables.tableprefix#threads.views) as views, but it returns a zero flat instead of, say, 8, (5+3). Thank You. Walter conti contiw wrote: > > The following query run flawlessly in localhost but produces error on ISP > server: > You have an error in your SQL syntax; check the manual that corresponds to > your MySQL server version for the right syntax to use near 'distinct > sf_threads.views) as views, ((count(distinct sf_messages.' at line 6 > > The actual behaviour here: > http://eduforums.us or > http://wconti.com/schoolsforums/index.cfm > > I am using the exact same database both in local and server. > Running MySQL 5 in localhost and supposedly versions 4 and 5 sopported by > the ISP server. > > Thanks for helping > > ------------ > > SELECT > #variables.tableprefix#conferences.id, > #variables.tableprefix#conferences.name, > count(distinct #variables.tableprefix#forums.id)-1 as schools, > count(distinct #variables.tableprefix#threads.id) as topics, > count(distinct #variables.tableprefix#messages.id) as msgs, > sum(distinct #variables.tableprefix#threads.views) as views, > ((count(distinct #variables.tableprefix#messages.id) *2) +sum(distinct > #variables.tableprefix#threads.views) ) as activity, > 0 as hBarLength > FROM ((#variables.tableprefix#conferences > left JOIN #variables.tableprefix#forums ON > #variables.tableprefix#conferences.id = > #variables.tableprefix#forums.conferenceidfk) > left JOIN #variables.tableprefix#threads ON > #variables.tableprefix#forums.id = > #variables.tableprefix#threads.forumidfk and > #variables.tableprefix#threads.author <> 'admin') > left JOIN #variables.tableprefix#messages ON > #variables.tableprefix#threads.id = > #variables.tableprefix#messages.threadidfk and > #variables.tableprefix#messages.author <> 'admin' > GROUP BY #variables.tableprefix#conferences.id > > -- View this message in context: http://www.nabble.com/Query-OK--in-localhost%2C-error-on-ISP-server-tp16738784p16753509.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
