Mysql – GROUP_CONCAT’s Rows to Columns

MySQL

I have this table with 16 rows only:

http://www.jassimrahma.com/temp/attendence_table.png

and I am using below code now to split the time of attendance into columns and getting this result:

http://www.jassimrahma.com/temp/attendence_result.png

but I am not happy with it! for example, 7th July, there is only one fingerprint so it should be only F1 but it's repeating it in F1, F3 and F4.

SELECT DATE(attendance_date_time) AS attendance_date, SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 1), ',', -1) AS F1,
IF(LENGTH(GROUP_CONCAT(TIME(attendance_date_time))) - LENGTH(REPLACE(GROUP_CONCAT(TIME(attendance_date_time)), ',', '')) > 1,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 2), ',', -1) ,NULL) AS F2,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 3), ',', -1) AS F3,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 4), ',', -1) AS F4
FROM employee_attendance
GROUP BY DATE(attendance_date_time);

How can I fix this please?

Thanks,
Jassim

Best Answer

SUBSTRING_INDEX will repeat the value if the value doesnt not end with the respective delimiter,so just concat a comma at the end of each string

SELECT DATE(attendance_date_time) AS attendance_date, SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(TIME(attendance_date_time)),','), ',', 1), ',', -1) AS F1_time,
IF(LENGTH(GROUP_CONCAT(TIME(attendance_date_time))) - LENGTH(REPLACE(GROUP_CONCAT(TIME(attendance_date_time)), ',', '')) > 1,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(TIME(attendance_date_time)),','), ',', 2), ',', -1), NULL) AS F2_time,
-- SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 3), ',', -1) AS F3_time,
IFNULL(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(TIME(attendance_date_time)),','), ',', 3), ',', -1), NULL) AS F3_time,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(TIME(attendance_date_time)),','), ',', 4), ',', -1) AS F4_time
FROM employee_attendance
GROUP BY DATE(attendance_date_time);

FIDDLE

Related Solutions

MySQL to PostgreSQL Migration – How to Translate Databases Efficiently

Something like this:

SELECT coalesce(CONCAT('Component ', t.component), 'Total for all Components') AS __group__,
       CASE
          WHEN t.id > 0 THEN CASE t.status WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;' END
          ELSE 'font-weight: bold'
        END AS __style__,
        t.id AS ticket,
        case when t.id > 0 then t.summary else 'Total' end AS summary,
        SUM( coalesce(w.endtime, current_timestamp) - w.starttime) AS total
FROM ticket t
  INNER JOIN work_log w ON t.id = w.ticket
GROUP BY t.component, t.id, t.summary, t.status

The sum() will sum up the difference in milliseconds between endtime and starttime not sure what the intention behind that is.

I'm not entirely sure what the rollup does, but something along the lines:

with base_data as (
   ... the above query goes here ...
)
select __group__, ticket, __style__, summary, total
from base_data
union all
select null, null, null, null, sum(total)
from base_data
group by __group__, ticket, __style__, summary

might get you started.

Mysql speed issues with SHOW COLUMNS or SHOW FIELDS

Would you consider a RAM disk for temp tables ?

First, create the RAM disk (16GB)

mkdir /var/tmpfs
echo "none   /var/tmpfs              tmpfs   defaults,size=16g        1 2" >> /etc/fstab
mount -t tmpfs -o size=16g none /var/tmpfs

Next, add this to my.cnf

[mysqld]
tmpdir=/var/tmpfs

Then, just restart mysql

service mysql restart

Now, all temp tables will land in RAM

I have discussed this before

Oct 09, 2012 : Inserting query result to another table hangs on "Copying to temp table on disk" on MySQL
Nov 30, 2012 : Is it bad to create many mysql temporary tables simultaneously?
Jan 04, 2013 : Is there a MySQL engine or trick to avoid writing so many temp tables to disk?
Nov 15, 2013 : MySQL creates temporary tables on disk. How do I stop it?

Best Answer

Related Solutions

MySQL to PostgreSQL Migration – How to Translate Databases Efficiently

Mysql speed issues with SHOW COLUMNS or SHOW FIELDS

Give it a Try !!!

Related Question