While display group of table value in mysql it shows each and every value as ‘0’

my table

mysql> desc attendance;
+--------+------------+------+-----+---------+----------------+
| Field  | Type       | Null | Key | Default | Extra          |
+--------+------------+------+-----+---------+----------------+
| aid    | bigint(20) | NO   | PRI | NULL    | auto_increment |
| sid    | int(10)    | YES  | MUL | NULL    |                |
| cid    | bigint(20) | YES  | MUL | NULL    |                |
| ttid   | bigint(20) | YES  | MUL | NULL    |                |
| did    | int(3)     | YES  | MUL | NULL    |                |
| date   | date       | YES  |     | NULL    |                |
| hour   | varchar(3) | YES  |     | NULL    |                |
| stuid  | bigint(20) | YES  | MUL | NULL    |                |
| status | varchar(8) | YES  |     | NULL    |                |
+--------+------------+------+-----+---------+----------------+
9 rows in set (0.47 sec)

and it’s values are

mysql> select * from attendance;
+-----+------+------+------+------+------------+------+-------+---------+
| aid | sid  | cid  | ttid | did  | date       | hour | stuid | status  |
+-----+------+------+------+------+------------+------+-------+---------+
|   1 |    2 |   13 |    4 |    3 | 2020-03-25 | p1   |    16 | present |
|   2 |    2 |   13 |    4 |    3 | 2020-03-25 | p1   |    17 | absent  |
|   3 |    2 |   13 |    4 |    3 | 2020-03-25 | p1   |    18 | present |
|   4 |    2 |   13 |    4 |    3 | 2020-03-25 | p1   |    19 | absent  |
|   5 |    2 |   13 |    4 |    3 | 2020-03-25 | p1   |    20 | present |
|   6 |    2 |   13 |    4 |    3 | 2020-03-25 | p1   |    15 | absent  |
|   7 |    2 |   13 |    2 |    2 | 2020-03-17 | p6   |    16 | present |
|   8 |    2 |   13 |    2 |    2 | 2020-03-17 | p6   |    17 | absent  |
|   9 |    2 |   13 |    2 |    2 | 2020-03-17 | p6   |    18 | present |
|  10 |    2 |   13 |    2 |    2 | 2020-03-17 | p6   |    19 | absent  |
|  11 |    2 |   13 |    2 |    2 | 2020-03-17 | p6   |    20 | present |
|  12 |    2 |   13 |    2 |    2 | 2020-03-17 | p6   |    15 | absent  |
|  13 |    2 |   13 |    4 |    3 | 2020-03-25 | p2   |    16 | present |
|  14 |    2 |   13 |    4 |    3 | 2020-03-25 | p2   |    17 | present |
|  15 |    2 |   13 |    4 |    3 | 2020-03-25 | p2   |    18 | present |
|  16 |    2 |   13 |    4 |    3 | 2020-03-25 | p2   |    19 | present |
|  17 |    2 |   13 |    4 |    3 | 2020-03-25 | p2   |    20 | present |
|  18 |    2 |   13 |    4 |    3 | 2020-03-25 | p2   |    15 | present |
|  19 |    2 |   13 |    4 |    3 | 2020-03-25 | p8   |    16 | absent  |
|  20 |    2 |   13 |    4 |    3 | 2020-03-25 | p8   |    17 | absent  |
|  21 |    2 |   13 |    4 |    3 | 2020-03-25 | p8   |    18 | present |
|  22 |    2 |   13 |    4 |    3 | 2020-03-25 | p8   |    19 | present |
|  23 |    2 |   13 |    4 |    3 | 2020-03-25 | p8   |    20 | present |
|  24 |    2 |   13 |    4 |    3 | 2020-03-25 | p8   |    15 | absent  |
+-----+------+------+------+------+------------+------+-------+---------+
24 rows in set (0.00 sec)

Now, i am going to grouping table but returns value in 0 While group the table it returns each and every column value returns in 0. Why that is happened. I need present or absent instead of 0. Can anyone solve this logic error?

Answer

sum(case when hour = 'P1' then status else 'null' end) p1

The problem is that you are suming numbers and litteral string 'null'.

You wanted null (without quotes) – or better yet, 0

sum(case when hour = 'p1' then status else 0 end) p1

Note that in MySQL evaluates true/false conditions as 1/0 in numeric context, which allows shortening the expression like:

sum(hour = 'p1') p1