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

**newphpcoder** · Mar 27, 2012 01:58

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.

**r937** · Mar 27, 2012 02:17

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

**newphpcoder** · Mar 28, 2012 18:45

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

**r937** · Mar 28, 2012 19:29

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

**newphpcoder** · Mar 28, 2012 19:57

when i remove the GROUP BY the output is NULL.

sorry I don't understand the logic

Thank you

**newphpcoder** · Mar 28, 2012 21:16

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