#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0

    MySQL query, no experience


    I want to be able to turn this:

    http://i48.tinypic.com/6h16x2.png

    Into this:

    http://i45.tinypic.com/25ahsn7.png

    By running a MySQL query. There will be up to 100 different names/accounts that will need to be calculated. I have very little experience in MySQL, mainly because I've never had to use it until now, and even then, this is all I'd need to use it for. There are other ways I could go about this, but for simplicity's sake, let's say it must be done using MySQL.

    I know it'd be fairly simple to do for someone experienced in MySQL, and I'd learn to do it myself, but I am somewhat on a short schedule, a few days actually.

    Can anyone help me out?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    41
    Rep Power
    10
    Is there any rationale for how the changes are made - any pattern - there's not enough information given to even begin to help
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0
    This is actually just a .csv file. It is formatted that way just because that is how it is pulled from the database. I just want to run a query on it in order to add the total times of "duration".
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    41
    Rep Power
    10
    Your answer still is uncleared based on the example you provided - you changed one link into another link.

    The first step is getting your CSV file into a database table, the next is being clear on what information you want to work with and change. What I see in the example you posted is a change in the link
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0
    You do realize that the link is a LINK to what I want to change, right? New members can't post links to URLS, so I put it in code tags...
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    41
    Rep Power
    10
    Actually since you posted very little information in your ooriginal post - it's very difficult to tell WHAT you need - if you have user input now you are beyond MySQL

    Good luck to you
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0
    What are you talking about? There is no user input.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    rolf, calm down, your post was somewhat misleading

    i, too, at first misunderstood what you wanted -- it looked like you wanted to change i48 to i45 and 6h16x2 to 25ahsn7

    once i realized you wanted us to actually go to those tinypic web pages and look at what's there, it became clear
    Code:
    SELECT `agent name`
         , username
         , DATE(`login time`) AS date
         , SEC_TO_TIME(SUM(TIME_TO_SEC(duration))) AS duration
      FROM daTable
    GROUP
        BY `agent name`
         , username
         , DATE(`login time`)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0
    I thought jemagee was just screwing with me.

    Basically, I have a .csv file, need to run a MySQL query on it to get the total duration of each agent and then have it exported back to a .csv

    I am not comfortable using MySQL, but the person who wants this done, wants it done using MySQL.
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by RolfPower
    Basically, I have a .csv file, need to run a MySQL query on it ...
    have you loaded the csv file into a mysql table yet?

    you would use the LOAD DATA INFILE command to do that

    you can pre-define the table and its columns, and it's easier if you use column names that don't contain spaces

    then try the query i posted (with changed column names as appropriate)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0
    I have not yet put it in a MySQL table.

    I forgot to add that the images linked to above were without the proper formatting, after formatted correctly, they are in the proper columns:

    http://i50.tinypic.com/68czrt.png
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0
    Trying to load the CSV file into a table, I get:

    Error Code: 1366. Incorrect integer value: ''Agent Name'' for column 'Agent Name' at row 1

    Using:

    Code:
    LOAD DATA INFILE '1.csv' 
    INTO TABLE test
    FIELDS TERMINATED BY ',' 
    LINES TERMINATED BY '\n'
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0
    Actually, from messing around, and going by what you said, I've figured things out.

    Thanks for the help. I have gone from completely uninterested in MySQL to wanting to know more.

IMN logo majestic logo threadwatch logo seochat tools logo