CODEWITHSUNDEEP
mysql
Ferdian
Ferdian

Reputation: 76

Can I Use Count Case to Display result of Conditional Count in MySQL?

I have database table called data in my database table structure like this : 

+----+------------+------+--------+---------+
| id |    date    | name | absent | late_in |
+----+------------+------+--------+---------+
|  1 | 2016-09-09 | John | Hadir  |      20 |
|  2 | 2016-09-09 | Sean | Hadir  |         |
|  3 | 2016-09-09 | Alea | Libur  |         |
|  4 | 2016-09-09 | Rina | Hadir  |         |
|  5 | 2016-09-08 | John | Hadir  |         |
|  6 | 2016-09-08 | Sean | Ijin   |         |
|  7 | 2016-09-08 | Alea | Hadir  |         |
|  8 | 2016-09-08 | Rina | Hadir  |      10 |
|  9 | 2016-09-07 | John | Sakit  |         |
| 10 | 2016-09-07 | Sean | Hadir  |         |
| 11 | 2016-09-07 | Alea | Hadir  |         |
| 12 | 2016-09-07 | Rina | Hadir  |         |
+----+------------+------+--------+---------+

and I want to display in Html to this :

+------+-------+-------+------+------+-------+---------+
| Name | Hadir | Sakit | Ijin | Alpa | Libur | late_in |
+------+-------+-------+------+------+-------+---------+
| John |     2 |     1 |    - |    - |     - | 20 Min  |
| Sean |     2 |     - |    1 |    - |     - |      -  |
| Alea |     2 |     - |    - |    - |     1 |      -  |
| Rina |     3 |     - |    - |    - |     - | 10 Min  |
+------+-------+-------+------+------+-------+---------+

I've try using this query, 

SELECT date, name, absent,
SUM(late_in) as late,
COUNT(CASE WHEN absent = 'Hadir' THEN absent ELSE 0 END) AS hadir, 
COUNT(CASE WHEN absent = 'Sakit' THEN absent ELSE 0 END) AS sakit, 
COUNT(CASE WHEN absent = 'Izin/Cuti' THEN absent ELSE 0 END) AS izin,
COUNT(CASE WHEN absent = 'Alpha' THEN absent ELSE 0 END) AS alpha,
FROM data GROUP BY name WHERE date between '$foo' and '$foo2'

but I don't know why, there's no data to display.

Upvotes: 1

Views: 65

Answers (1)

JLikho
JLikho

Reputation: 51

SELECT DISTINCT
name as 'Name ',
SUM(CASE WHEN absent='Hadir' THEN 1 ELSE null END) as 'Hadir',
SUM(CASE WHEN absent='Sakit' THEN 1 ELSE null END) as 'Sakit',
SUM(CASE WHEN absent='Ijin' THEN 1 ELSE null END) as 'Ijin',
SUM(CASE WHEN absent='Alpa' THEN 1 ELSE null END) as 'Alpa',
SUM(CASE WHEN absent='Libur' THEN 1 ELSE null END) as 'Libur',
SUM(late_in) as 'late_in'
FROM data
GROUP BY name
ORDER BY id ASC

Upvotes: 1

Related Questions