This type of rotation of data from columns to rows is known as a PIVOT. MySQL does not have a pivot function but you can use an aggregate function with a CASE expression to get the result.
My first suggestion would be to determine if you have a calendar
table or a table that contains all of the dates that you want to display. If not, then I would suggest creating one similar to the following:
CREATE TABLE calendar (`Date` datetime) ;
INSERT INTO calendar (`Date`)
VALUES
('2013-06-01 00:00:00'),
('2013-06-02 00:00:00'),
('2013-06-03 00:00:00'),
('2013-06-04 00:00:00'),
('2013-06-05 00:00:00'),
('2013-06-06 00:00:00'),
('2013-06-07 00:00:00'),
('2013-06-08 00:00:00'),
('2013-06-09 00:00:00'),
('2013-06-10 00:00:00');
This will make it possible for you to generate a list of all of the dates that you want to display.
Second, you will need to generate the list of each student and each date. You can do this by using a CROSS JOIN between your tbl_admission
and the calendar
table:
select c.date, a.studentname, a.rollno, a.class
from calendar c
cross join tbl_admission a;
See Demo. Once you have this list, then you can use a LEFT JOIN to your existing tbl_absentees
table to get the result:
select
ca.studentname,
ca.rollno,
ca.class,
max(case when ca.date = '2013-06-01' then coalesce(p.status, 'P') end) `2013-06-01`,
max(case when ca.date = '2013-06-02' then coalesce(p.status, 'P') end) `2013-06-02`,
max(case when ca.date = '2013-06-03' then coalesce(p.status, 'P') end) `2013-06-03`,
max(case when ca.date = '2013-06-04' then coalesce(p.status, 'P') end) `2013-06-04`,
max(case when ca.date = '2013-06-05' then coalesce(p.status, 'P') end) `2013-06-05`,
max(case when ca.date = '2013-06-06' then coalesce(p.status, 'P') end) `2013-06-06`,
max(case when ca.date = '2013-06-07' then coalesce(p.status, 'P') end) `2013-06-07`,
max(case when ca.date = '2013-06-08' then coalesce(p.status, 'P') end) `2013-06-08`,
max(case when ca.date = '2013-06-08' then coalesce(p.status, 'P') end) `2013-06-09`,
max(case when ca.date = '2013-06-10' then coalesce(p.status, 'P') end) `2013-06-10`
from
(
select c.date, a.studentname, a.rollno, a.class
from calendar c
cross join tbl_admission a
) ca
left join tbl_absentees p
on ca.rollno = p.rollno
and ca.date = p.date
group by ca.studentname, ca.rollno, ca.class
order by ca.rollno;
See SQL Fiddle with Demo. Of course for your request, you most likely want to query the data based on a date range so you will not want to hard-code the values. If that is the case, then you will need to look at using a prepared statement to generate dynamic SQL:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(CASE WHEN ca.date = ''',
date_format(date, '%Y-%m-%d'),
''' THEN coalesce(p.status, ''P'') END) AS `',
date_format(date, '%Y-%m-%d'), '`'
)
) INTO @sql
FROM calendar
where date>='2013-06-01'
and date <= '2013-06-05';
SET @sql
= CONCAT('SELECT ca.studentname,
ca.rollno,
ca.class, ', @sql, '
from
(
select c.date, a.studentname, a.rollno, a.class
from calendar c
cross join tbl_admission a
) ca
left join tbl_absentees p
on ca.rollno = p.rollno
and ca.date = p.date
where ca.date>=''2013-06-01''
and ca.date <= ''2013-06-05''
group by ca.studentname, ca.rollno, ca.class
order by ca.rollno');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
See SQL Fiddle with Demo. Both of these queries will give a result similar to:
| STUDENTNAME | ROLLNO | CLASS | 2013-06-01 | 2013-06-02 | 2013-06-03 | 2013-06-04 | 2013-06-05 | 2013-06-06 | 2013-06-07 | 2013-06-08 | 2013-06-09 | 2013-06-10 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Naren | 1 | 22 | A | A | A | A | P | P | P | P | P | P |
| Srinu | 2 | 22 | P | P | P | P | P | P | P | P | P | P |
| Blah | 3 | 22 | A | P | P | P | P | P | P | P | P | P |
I have a Dynamic SQL solution
PROPOSED QUERY
SET group_concat_max_len = 1048576;
SET @GivenDB = 'mydb';
SET @GivenTable = 'mytable';
SELECT CONCAT('SELECT ',GROUP_CONCAT(CONCAT('COUNT(',column_name,
')*100/COUNT(1) ',column_name,'_pct')),
' FROM ',table_schema,'.',table_name) sqlstmt
INTO @sql FROM information_schema.columns
WHERE table_schema=@GivenDB
AND table_name=@GivenTable;
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
EXAMPLE
Let's use a sample table I made up for another question
mysql> use timyash
Database changed
mysql> show tables;
+-------------------+
| Tables_in_timyash |
+-------------------+
| mytable |
+-------------------+
1 row in set (0.00 sec)
mysql> desc mytable;
+--------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| transmitted | int(11) | NO | | NULL | |
| connect_time | int(11) | NO | | NULL | |
+--------------+---------+------+-----+---------+----------------+
3 rows in set (0.02 sec)
mysql> select * from mytable;
+----+-------------+--------------+
| id | transmitted | connect_time |
+----+-------------+--------------+
| 1 | 2650131 | 117987 |
| 2 | 6465178 | 78073 |
| 3 | 25905117 | 159268 |
| 4 | 59178089 | 410282 |
| 5 | 73502942 | 596408 |
| 6 | 75695427 | 683045 |
| 7 | 77576167 | 740379 |
+----+-------------+--------------+
7 rows in set (0.00 sec)
mysql>
PROPOSED QUERY DISPLAYED
mysql> SET group_concat_max_len = 1048576;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @GivenDB = 'timyash';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @GivenTable = 'mytable';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT CONCAT('SELECT ',GROUP_CONCAT(CONCAT('COUNT(',column_name,
-> ')*100/COUNT(1) ',column_name,'_pct')),
-> ' FROM ',table_schema,'.',table_name) sqlstmt
-> INTO @sql FROM information_schema.columns
-> WHERE table_schema=@GivenDB
-> AND table_name=@GivenTable;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT @sql\G
*************************** 1. row ***************************
@sql: SELECT COUNT(id)*100/COUNT(1) id_pct,COUNT(transmitted)*100/COUNT(1) transmitted_pct,COUNT(connect_time)*100/COUNT(1) connect_time_pct FROM timyash.mytable
1 row in set (0.00 sec)
mysql>
PROPOSED QUERY EXECUTED
mysql> SET group_concat_max_len = 1048576;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @GivenDB = 'timyash';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @GivenTable = 'mytable';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT CONCAT('SELECT ',GROUP_CONCAT(CONCAT('COUNT(',column_name,
-> ')*100/COUNT(1) ',column_name,'_pct')),
-> ' FROM ',table_schema,'.',table_name) sqlstmt
-> INTO @sql FROM information_schema.columns
-> WHERE table_schema=@GivenDB
-> AND table_name=@GivenTable;
Query OK, 1 row affected (0.02 sec)
mysql> PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
+----------+-----------------+------------------+
| id_pct | transmitted_pct | connect_time_pct |
+----------+-----------------+------------------+
| 100.0000 | 100.0000 | 100.0000 |
+----------+-----------------+------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
PROPOSED QUERY EXECUTED WITH NULL DATA
Let's change two rows to have NULL column data
mysql> alter table mytable modify column connect_time INT NULL;
Query OK, 0 rows affected (0.66 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> update mytable set connect_time = 1/0 WHERE id in (2,5);
Query OK, 2 rows affected (0.06 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from mytable;
+----+-------------+--------------+
| id | transmitted | connect_time |
+----+-------------+--------------+
| 1 | 2650131 | 117987 |
| 2 | 6465178 | NULL |
| 3 | 25905117 | 159268 |
| 4 | 59178089 | 410282 |
| 5 | 73502942 | NULL |
| 6 | 75695427 | 683045 |
| 7 | 77576167 | 740379 |
+----+-------------+--------------+
7 rows in set (0.00 sec)
mysql>
Let's run it again
mysql> SET group_concat_max_len = 1048576;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @GivenDB = 'timyash';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @GivenTable = 'mytable';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT CONCAT('SELECT ',GROUP_CONCAT(CONCAT('COUNT(',column_name,
-> ')*100/COUNT(1) ',column_name,'_pct')),
-> ' FROM ',table_schema,'.',table_name) sqlstmt
-> INTO @sql FROM information_schema.columns
-> WHERE table_schema=@GivenDB
-> AND table_name=@GivenTable;
Query OK, 1 row affected (0.03 sec)
mysql> PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
+----------+-----------------+------------------+
| id_pct | transmitted_pct | connect_time_pct |
+----------+-----------------+------------------+
| 100.0000 | 100.0000 | 71.4286 |
+----------+-----------------+------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
GIVE IT A TRY !!!
UPDATE 2014-12-29 15:25 EST
In response to your last comment, use this version
SET group_concat_max_len = 1048576;
SET @GivenDB = 'timyash';
SET @GivenTable = 'mytable';
SELECT CONCAT('SELECT ',
GROUP_CONCAT(CONCAT('SUM(IF(IFNULL(',column_name,
','''')='''',0,1))*100/COUNT(1) ',column_name,'_pct')),
' FROM ',table_schema,'.',table_name) sqlstmt
INTO @sql FROM information_schema.columns
WHERE table_schema=@GivenDB
AND table_name=@GivenTable;
SELECT @sql\G
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
You do not need to check the datatype with this one.
To show you that it works ...
mysql> SET group_concat_max_len = 1048576;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @GivenDB = 'timyash';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @GivenTable = 'mytable';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT CONCAT('SELECT ',
-> GROUP_CONCAT(CONCAT('SUM(IF(IFNULL(',column_name,
-> ','''')='''',0,1))*100/COUNT(1) ',column_name,'_pct')),
-> ' FROM ',table_schema,'.',table_name) sqlstmt
-> INTO @sql FROM information_schema.columns
-> WHERE table_schema=@GivenDB
-> AND table_name=@GivenTable;
Query OK, 1 row affected (0.03 sec)
mysql> SELECT @sql\G
*************************** 1. row ***************************
@sql: SELECT SUM(IF(IFNULL(id,'')='',0,1))*100/COUNT(1) id_pct,SUM(IF(IFNULL(transmitted,'')='',0,1))*100/COUNT(1) transmitted_pct,SUM(IF(IFNULL(connect_time,'')='',0,1))*100/COUNT(1) connect_time_pct FROM timyash.mytable
1 row in set (0.00 sec)
mysql> PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
+----------+-----------------+------------------+
| id_pct | transmitted_pct | connect_time_pct |
+----------+-----------------+------------------+
| 100.0000 | 100.0000 | 71.4286 |
+----------+-----------------+------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
Best Answer
So grouping by emp_id, month and suming the count based on
atten_type
?