SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql error: for the right syntax to use near ')

    Hi

    I got an error:

    Error Code : 1064
    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 '), NOW()),' day(s) ', TIMESTAMPDIFF(HOUR, AVG(chemicalweighing_dateEntry), NOW()' at line 1
    (0 ms taken)

    from my query:

    Code:
    SELECT (concat(TIMESTAMPDIFF(DAY, AVG(), NOW()),' day(s) ', TIMESTAMPDIFF(HOUR, AVG(chemicalweighing_dateEntry), NOW()) - (TIMESTAMPDIFF(DAY, AVG(chemicalweighing_dateEntry), NOW()) * 24), ' hr(s) ago')) AS AGE FROM kanban_data GROUP BY chemicalweighing_dateEntry;
    I just need to get the aging.


    Thank you.
    Attached Images Attached Images

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    dude, the error message is telling you exactly where to look for your error

    here --
    Code:
    AVG()
    you can't use the AVG function without an argument, i.e. you actually have to average something
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I revised my code to this:
    Code:
    SELECT (concat(TIMESTAMPDIFF(DAY, AVG(chemicalweighing_dateEntry), NOW()),' day(s) ', TIMESTAMPDIFF(HOUR, AVG(chemicalweighing_dateEntry), NOW()) - (TIMESTAMPDIFF(DAY, AVG(chemicalweighing_dateEntry), NOW()) * 24), ' hr(s) ago')) AS AGE FROM kanban_data GROUP BY chemicalweighing_dateEntry;
    and i also try instead of AVG I use MAX
    Code:
    SELECT (concat(TIMESTAMPDIFF(DAY, MAX(chemicalweighing_dateEntry), NOW()),' day(s) ', TIMESTAMPDIFF(HOUR, MAX(chemicalweighing_dateEntry), NOW()) - (TIMESTAMPDIFF(DAY, MAX(chemicalweighing_dateEntry), NOW()) * 24), ' hr(s) ago')) AS AGE FROM kanban_data GROUP BY chemicalweighing_dateEntry;
    and they are same output.

    and I attach the result.

    Now I want to know how can I can the sum of ALL this data.


    I need a query again to get the sum or I need output is : 67 day(s) 200 hr(s) ago.

    Thank you so much
    Attached Images Attached Images

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    consider what this means --
    Code:
    GROUP BY chemicalweighing_dateEntry
    this means your result from the query will have one row for every different value of chemicalweighing_dateEntry

    suppose there are 9 rows where chemicalweighing_dateEntry = 37

    (i know it's supposed to be a date but i'm trying to teach you the concept of grouping here)

    so you have 9 values, and all of them are equal to 37

    these values form a group

    what is the average value in those 9 values? it's gotta be 37

    also, what is the maximum value in those 9 values? it's also gotta be 37

    i request that you go away until you understand what i've just explained, and how it applies to your query with chemicalweighing_dateEntry as the grouping column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    when i remove the GROUP BY the output is NULL.

    sorry I don't understand the logic

    Thank you

  6. #6
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried this:

    Code:
    SELECT(concat(SUM(TIMESTAMPDIFF(DAY, chemicalweighing_dateEntry, NOW())),' day(s) '), SUM(TIMESTAMPDIFF(HOUR, chemicalweighing_dateEntry, NOW()) - (TIMESTAMPDIFF(DAY, chemicalweighing_dateEntry, NOW()) * 24), ' hr(s) ago')) AS AGE FROM kanban_data;
    and I got an error:

    Error Code : 1241
    Operand should contain 1 column(s)
    (0 ms taken)


    I can't figured out where I am wrong
    Thank you

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    I can't figured out where I am wrong
    you removed the GROUP BY clause

    please don't come back until you understand how GROUP BY works
    rudy.ca | @rudydotca
    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
  •