SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE `ps_bonus_account` ( `id_bonusaccount` int(11) NOT NULL, `id_customer` int(10) UNSIGNED NOT NULL, `id_order` int(10) UNSIGNED NOT NULL, `id_cart_rule` int(10) UNSIGNED NOT NULL, `points` decimal(20,6) NOT NULL DEFAULT '0.000000', `comment` text NOT NULL, `status` int(10) UNSIGNED NOT NULL, `paid` tinyint(1) UNSIGNED NOT NULL DEFAULT '0', `type` varchar(20) NOT NULL, `date_add` datetime DEFAULT NULL, `date_bonus_to` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `ps_bonus_account` (`id_bonusaccount`, `id_customer`, `id_order`, `id_cart_rule`, `points`, `comment`, `status`, `paid`, `type`, `date_add`, `date_bonus_to`) VALUES (218, 518, 278, 50, '5.000000', 'Бонусы за заказ', 1, 1, 'order', '2021-11-09 21:45:16', '2022-12-09 21:45:16'), (219, 518, 278, 50, '10.000000', 'Оплата заказа', 2, 1, 'order', '2021-11-09 21:45:17', '2022-12-09 21:45:16'), (220, 518, 0, 0, '100.000000', 'За регистрацию', 1, 1, 'no_order', '2021-11-09 21:57:40', '2021-11-08 21:57:40'); SELECT GREATEST(0, -- return 0 in case negative balance SUM( IF(`status` = 1 AND `date_bonus_to` > NOW() , `points`, 0) -- bonus added and not expired - IF(`status` = 2, `points`, 0) -- bonus used ) ) `balance` FROM `ps_bonus_account` WHERE `id_customer` = 518 AND `paid` = 1 GROUP BY `id_customer`