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?
Advertisement
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