SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE `rents` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `bills` CHAR(10) NULL, PRIMARY KEY (`id`), INDEX (`bills`) ); DELIMITER $$ CREATE PROCEDURE InsertBills(IN MinRows INT, IN MaxRows INT, IN Bills CHAR(10)) BEGIN DECLARE NumRows INT; DECLARE i INT; SET i = 1; SET NumRows = FLOOR(RAND() * (MaxRows - MinRows + 1)) + MinRows; START TRANSACTION; WHILE i <= NumRows DO INSERT INTO `rents` (`bills`) VALUES (Bills); SET i = i + 1; END WHILE; COMMIT; END$$ DELIMITER ; CALL InsertBills(10000,20000, "PAID"); CALL InsertBills(10000,20000, "DEBT"); CALL InsertBills(10000,20000, "CANCELLED"); set profiling=1; SELECT COUNT(1) `all` FROM `rents`; SELECT COUNT(1) `paid` FROM `rents` WHERE `bills` = 'PAID'; SELECT COUNT(1) `debt` FROM `rents` WHERE `bills` = 'DEBT'; SELECT COUNT(1) `cancelled` FROM `rents` WHERE `bills` = 'CANCELLED'; SELECT SUM(1) `all`, SUM(CASE WHEN `bills` = "PAID" THEN 1 ELSE 0 END) `paid`, SUM(CASE WHEN `bills` = "DEBT" THEN 1 ELSE 0 END) `debt`, SUM(CASE WHEN `bills` = "CANCELLED" THEN 1 ELSE 0 END) `cancelled` FROM `rents`; show profiles;