ere we have a table named car and we want to calculate how many NULL values are there in ‘check’ and ‘flag’ fields
CREATE TABLE IF NOT EXISTS `car` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`brand` varchar(255) NOT NULL,
`check` int(1) DEFAULT NULL,
`flag` int(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
–
– Dumping data for table `car`
–
INSERT INTO `car` (`id`, `brand`, `check`, `flag`) VALUES
(1, ‘Porsche’, NULL, 1),
(2, ‘Renault’, 1, NULL),
(3, ‘sadsa’, NULL, 1),
(4, ‘asdasd’, NULL, NULL);
Query run
————————–
select
sum(@chk:=if(ISNULL(`check`),0,@chk)+1) as CHKTOTAL,
sum(@flg:=if(ISNULL(`flag`),0,@flg)+1) as FLGTOTAL
FROM `car`
Returns
————————-
CHKTOTAL FLGTOTAL
3.000000 2.000000