Re: Query OK in localhost, error on ISP server
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 #variables.tableprefix#threads.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. -- View this message in context: http://www.nabble.com/Query-OK--in-localhost%2C-error-on-ISP-server-tp16738784p16753507.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]
Re: Query OK in localhost, error on ISP server
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]
Query OK in localhost, error on ISP server
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-tp16738784p16738784.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]