Hi…
I need help in concatenation.
here is my 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
oddz
March 29, 2012, 7:59am
2
It would be best to apply the labels in the application language rather than the query itself.
When I tried this query:
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)
r937
March 29, 2012, 9:01am
4
newphpcoder:
I got an error:
count the number of parentheses – they are unbalanced
and really, you should solve your GROUP BY problem first
I count the parenthesis and its equal.
Thank you
SELECT
(
CONCAT(
SUM(TIMESTAMPDIFF
(
DAY, chemicalweighing_dateEntry, NOW()
)
)
, ' days'
)
, (
(
SUM(
TIMESTAMPDIFF(
HOUR, chemicalweighing_dateEntry, NOW()
)
)
)
- (
SUM(
TIMESTAMPDIFF(
DAY,chemicalweighing_dateEntry, NOW()
)
) * 24
), ' hrs'
)
)
If I relay that so it’s not one line, it makes it easier to see what pairs up with what, from that you can see a stray ) which is “ending” the CONCAT() function early. In any case I agree with oddz that it’s best to do the concatenation in whatever app language that you’re using.
When I revised it:
SELECT
(
CONCAT(
SUM(TIMESTAMPDIFF
(
DAY, chemicalweighing_dateEntry, NOW()
)
)
, ' days'
, (
(
SUM(
TIMESTAMPDIFF(
HOUR, chemicalweighing_dateEntry, NOW()
)
)
)
- (
SUM(
TIMESTAMPDIFF(
DAY,chemicalweighing_dateEntry, NOW()
)
) * 24
)
)
, ' hrs' AS aging
FROM kanban_data
)
)
?>
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 'FROM kanban_data
)
)' at line 55
(0 ms taken)
Thank you
r937
March 30, 2012, 8:31am
8
you cannot put parentheses around the FROM clause
how long have you been writing sql?
I resolved it using this code:
SELECT
(
CONCAT(
SUM(TIMESTAMPDIFF
(
DAY, chemicalweighing_dateEntry, NOW()
)
)
, ' days'
, (
(
SUM(
TIMESTAMPDIFF(
HOUR, chemicalweighing_dateEntry, NOW()
)
)
)
- (
SUM(
TIMESTAMPDIFF(
DAY,chemicalweighing_dateEntry, NOW()
)
) * 24
)
)
, ' hrs'
)
)AS aging
FROM kanban_data
Thank you