SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Fountain Valley, California
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL query ok locally but 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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Fountain Valley, California
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanx r937. Good fishing rod as usually. Appreciated.

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Fountain Valley, California
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •