SitePoint Sponsor

User Tag List

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

    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

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    It would be best to apply the labels in the application language rather than the query itself.
    The only code I hate more than my own is everyone else's.

  3. #3
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,330
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    I got an error:
    count the number of parentheses -- they are unbalanced

    and really, you should solve your GROUP BY problem first
    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)
    I count the parenthesis and its equal.

    Thank you

  6. #6
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,068
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Code MySQL:
    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.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  7. #7
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When I revised it:

    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   
            ) 
        )
    ?>
    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

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,330
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you cannot put parentheses around the FROM clause

    how long have you been writing sql?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I resolved it using this code:

    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


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
  •