MySQL query ok locally but error on ISP server

[Walt]

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

[r937]

SUM(DISTINCT xxxx) is valid?

i would rewrite the query, and push the aggregates into derived table subqueries

you appear to have multiple chained one-to-many relationships, which is why you deen DISTINCT

COUNT(DISTINCT xxxx) works when xxxx is a key, but SUM(DISTINCT xxxx), if it's even valid, is prone to errors when xxxx is a numeric value

for example, suppose you made several deposits to your bank account -- $5, $10, $20, $5, $5, $20 -- you wouldn't want your bank account to credit you with $35, right?

similarly, if multiple threads have 17 views each, you don't really want to sum the DISTINCT views, do you?

[Walt]

Thanx r937. Good fishing rod as usually. Appreciated.

[Walt]

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.

[r937]

if you would like help, you will need to explain all the one-to-many relationships in your tables

SHOW CREATE TABLE would be very helpful