Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Dec 2005
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MySQL query, no experience

    I want to be able to turn this:





    Into this:





    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?
    Last edited by cronic5; 10-28-2012 at 04:50 AM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Are those columns from different *TABLES* in the DB? Or all one table? (If one table, that's a bad DB design, if you care.)

    In any case you need to show us the SCHEMA of each table (or the table if only one).

    That is, the field names and the data types, by table.

    Where are the quote marks coming from, around the various names and values? None of them should be present in the database.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Dec 2005
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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".

  • #4
    New Coder
    Join Date
    Dec 2005
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

  • #5
    New Coder
    Join Date
    Dec 2005
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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'

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Last things first: The error message from the LOAD DATE INFILE means that your table test was not created correctly.

    For some reason, you created the field `Agent Name` as an INT(eger) field when clearly it should be VARCHAR(50) or something like that.

    But I don't understand this, at all.

    In your post #4 you wrote "This is actually just a .csv file. It is formatted that way just because that is how it is pulled from the database. " So if you pull the CSV file *FROM* the database WHY in the world are you now trying to put it BACK into the dataabase? I don't understand the reasoning behind that, at all.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    So you *SEEM* to be saying that http://i.stack.imgur.com/ASSvl.png shows the raw data in your table. You never bother to say what the name of the table is, you know.

    And I *THINK* you are saying that http://i.stack.imgur.com/gY8J1.png shows the results you want.

    If so, it's trivial.

    Code:
    SELECT `Agent Name`,`Username`,
           DATE(`Login Time`) AS `Date`,
           SUM ( TIMEDIFF( `Login Time`, `Logout Time` ) ) AS Duration
    FROM yourtablename
    GROUP BY `Agent Name`, `Username`, DATE(`Login Time`)
    ORDER BY `Agent Name`, `Date`
    Though this assumes the your Login Time and Logout Time fields in the table are indeed DATETIME values.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •