MySQL query, no experience

[cronic5]

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?

[Old Pedant]

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.

[cronic5]

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".

[cronic5]

http://i.stack.imgur.com/ASSvl.png

http://i.stack.imgur.com/gY8J1.png

Maybe this will clear some things up.

[cronic5]

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:

LOAD DATA INFILE '1.csv' 
INTO TABLE test
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'

[Old Pedant]

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.

[Old Pedant]

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.

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.