need help in concatenation

[newphpcoder]

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]

It would be best to apply the labels in the application language rather than the query itself.

[newphpcoder]

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]

I got an error:

count the number of parentheses -- they are unbalanced

and really, you should solve your GROUP BY problem first

[newphpcoder]

I count the parenthesis and its equal.

Thank you

[SpacePhoenix]

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.

[newphpcoder]

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]

you cannot put parentheses around the FROM clause

how long have you been writing sql?

[newphpcoder]

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