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/mysql?unsub=gcdmg-mysql-2@m.gmane.org

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/mysql?unsub=gcdmg-mysql-2@m.gmane.org

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/mysql?unsub=gcdmg-mysql-2@m.gmane.org