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]