I wrote a script that gather the space used by multiple datasets.
My database looks like the below:
| Date_and_Time | Server_Name_and_Drive | Space_Used_(Gb) |
|---|---|---|
| 08/04 00:00 | SERVER3 D DRIVE | 220 |
| 08/04 00:00 | SERVER2 D DRIVE | 10 |
| 08/04 00:00 | SERVER1 D DRIVE | 64 |
| 08/04 12:00 | SERVER3 D DRIVE | 221 |
| 08/04 12:00 | SERVER2 D DRIVE | 10 |
| 08/04 12:00 | SERVER1 D DRIVE | 67 |
| 09/04 00:00 | SERVER3 D DRIVE | 223 |
| 09/04 00:00 | SERVER2 D DRIVE | 11 |
| 09/04 00:00 | SERVER1 D DRIVE | 73 |
| 09/04 12:00 | SERVER3 D DRIVE | 225 |
| 09/04 12:00 | SERVER2 D DRIVE | 12 |
| 09/04 12:00 | SERVER1 D DRIVE | 75 |
Now, I want to get the data from my database. I am using the below to get a CSV out of it:
select * from myTable where Server_Name_and_Drive like "%DRIVE%" into outfile '/var/lib/mysql/myTable_date.csv' FIELDS TERMINATED BY ',';
This gives me the below output csv file:
08/04 00:00,SERVER3 D DRIVE,220
08/04 00:00,SERVER2 D DRIVE,10
08/04 00:00,SERVER1 D DRIVE,64
08/04 12:00,SERVER3 D DRIVE,221
08/04 12:00,SERVER2 D DRIVE,10
08/04 12:00,SERVER1 D DRIVE,67
09/04 00:00,SERVER3 D DRIVE,223
09/04 00:00,SERVER2 D DRIVE,11
09/04 00:00,SERVER1 D DRIVE,73
09/04 12:00,SERVER3 D DRIVE,225
09/04 12:00,SERVER2 D DRIVE,12
09/04 12:00,SERVER1 D DRIVE,75
Now, I would like to get the output differently and group the Date_and_Time, having only one line per same Date_and_Time, and the repeated row into column, such as below:
Date_and_Time,SERVER1 D DRIVE(Gb),SERVER2 D DRIVE(Gb),SERVER3 D DRIVE(Gb)
08/04 00:00,220,10,64
08/04 12:00,221,10,67
09/04 00:00,223,11,73
09/04 12:00,225,12,75
Is this possible using the "select" and "into outfile" to export it as CSV from the database?
Obviously, my table is much bigger and gather lots of different data, I simplify it this way to focus on the output I want.
Please, be kind as it is my first post here on stackoverflow.
Any help would be highly appreciated.
Regards, Nick
I tried to use pivot table on mysql but cannot make it to work.
database example:
| Date_Time | Server_drive | areaUsed |
|---|---|---|
| 2022-04-08 00:00:02 | /private_shares/SERVER01/DRIVES/G | 106953154560 |
| 2022-04-08 00:00:02 | /private_shares/SERVER02/DRIVES/F | 717575577600 |
| 2022-04-08 00:00:02 | /private_shares/SERVER03/DRIVES/D | 159979786240 |
| 2022-04-08 00:00:02 | /private_shares/SERVER04/DRIVES/W | 25792897024 |
| 2022-04-08 12:00:02 | /private_shares/SERVER01/DRIVES/G | 106953154560 |
| 2022-04-08 12:00:02 | /private_shares/SERVER02/DRIVES/F | 717718970368 |
| 2022-04-08 12:00:02 | /private_shares/SERVER03/DRIVES/D | 159979786240 |
| 2022-04-08 12:00:02 | /private_shares/SERVER04/DRIVES/W | 25792897024 |
| 2022-04-09 00:00:02 | /private_shares/SERVER01/DRIVES/G | 106953154560 |
| 2022-04-09 00:00:02 | /private_shares/SERVER02/DRIVES/F | 717718970368 |
| 2022-04-09 00:00:02 | /private_shares/SERVER03/DRIVES/D | 159981051904 |
| 2022-04-09 00:00:02 | /private_shares/SERVER04/DRIVES/W | 25792897024 |
| 2022-04-09 12:00:02 | /private_shares/SERVER01/DRIVES/G | 106953154560 |
| 2022-04-09 12:00:02 | /private_shares/SERVER02/DRIVES/F | 717874745344 |
| 2022-04-09 12:00:02 | /private_shares/SERVER03/DRIVES/D | 159977820160 |
| 2022-04-09 12:00:02 | /private_shares/SERVER04/DRIVES/W | 25792897024 |
I tried the following query but cannot make it to work:
SELECT areaUsage.Date_Time,
MAX(CASE WHEN areaUsage.Server_drive like "%SERVER01/DRIVES/G%" THEN areaUsage.areaUsed END) "SERVER01 DRIVE G",
MAX(CASE WHEN areaUsage.Server_drive like "%SERVER02/DRIVES/F%" THEN areaUsage.areaUsed END) "SERVER02 DRIVE F",
MAX(CASE WHEN areaUsage.Server_drive like "%SERVER03/DRIVES/D%" THEN areaUsage.areaUsed END) "SERVER03 DRIVE D",
MAX(CASE WHEN areaUsage.Server_drive like "%SERVER04/DRIVES/W%" THEN areaUsage.areaUsed END) "SERVER04 DRIVE W",
FROM areaUsage
GROUP BY areaUsage.Date_Time
ORDER BY areaUsage.Date_Time ASC;
Any idea what I am doing wrong?
The error output is as below:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM areaUsage GROUP BY areaUsage.currentTime' at line 1