Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2010
    Posts
    136

    Unanswered: 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 Thumbnails Attached Thumbnails chemicalweighing_dateEntry.jpg  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  3. #3
    Join Date
    Dec 2010
    Posts
    136
    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 Thumbnails Attached Thumbnails aging.jpg  
    Last edited by newphpcoder; 03-28-12 at 21:45.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  5. #5
    Join Date
    Dec 2010
    Posts
    136
    when i remove the GROUP BY the output is NULL.

    sorry I don't understand the logic

    Thank you

  6. #6
    Join Date
    Dec 2010
    Posts
    136
    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
    Join Date
    Dec 2010
    Posts
    136

    need help in concatenation

    Hi..

    I need help in concatenation.

    here is my code:

    Code:
    SELECT (SUM(TIMESTAMPDIFF(DAY, chemicalweighing_dateEntry, NOW()))), (SUM(TIMESTAMPDIFF(HOUR, chemicalweighing_dateEntry, NOW()))) - (SUM(TIMESTAMPDIFF(DAY,chemicalweighing_dateEntry, NOW())) * 24) AS age_day FROM kanban_data;
    the output of this is:

    57 204
    they are separated in between two columns.

    I want to combine them in one column like this:

    57 day(s) 204 hr(s) ago

    Thank you

  8. #8
    Join Date
    Dec 2010
    Posts
    136
    When I tried this query:

    Code:
    SELECT (CONCAT(SUM(TIMESTAMPDIFF(DAY, chemicalweighing_dateEntry, NOW())), ' days'), ((SUM(TIMESTAMPDIFF(HOUR, chemicalweighing_dateEntry, NOW()))) - (SUM(TIMESTAMPDIFF(DAY,chemicalweighing_dateEntry, NOW())) * 24), ' hrs'))
    I got an error:

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

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you have supplied more values to a function than it wa expecting
    go back to the manual and check what you are sending to the SQL engine

    if you can't work it out then break apart your SQL into chunks, prove easch chunk works, then gradually build the SQL back

    you don't need to do everything in SQL, you could do this sort of formatting in PHP. Use SQL to pre process the data but do the final formatting in PHP
    PHP: Date/Time Functions - Manual
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

Posting Permissions

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