Skip to content
Advertisement

subquery double where + aggreate functions

I have table and rows

CREATE TABLE `TEST_TRANSACTIONS`.`Users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `balance` INT NOT NULL,
  `payed` INT NOT NULL,
  `isSimpleUser` INT NOT NULL,
  `approvedAdminId` VARCHAR(45) DEFAULT NULL,
  PRIMARY KEY (`id`));


INSERT INTO `TEST_TRANSACTIONS`.`Users` (`id`, `balance`, `payed`, `isSimpleUser`) VALUES ('1', '1', '2', '0');
INSERT INTO `TEST_TRANSACTIONS`.`Users` (`id`, `balance`, `payed`, `isSimpleUser`) VALUES ('2', '2', '4', '0');
INSERT INTO `TEST_TRANSACTIONS`.`Users` (`id`, `balance`, `payed`, `isSimpleUser`, `approvedAdminId`) VALUES ('3', '10', '20', '1', '53ed3434asd');
INSERT INTO `TEST_TRANSACTIONS`.`Users` (`id`, `balance`, `payed`, `isSimpleUser`, `approvedAdminId`) VALUES ('4', '25', '35', '1', '35sdd2342ss');
INSERT INTO `TEST_TRANSACTIONS`.`Users` (`id`, `balance`, `payed`, `isSimpleUser`) VALUES ('5', '4444', '5555', '1');

I want to have sum of balance and payed for simple users with approvedAdminId (isSimpleUser = 1 AND approvedAdminId is not NULL) and for not simple users (isSimpleUser = 0)

Expected Result

sumBalancePremiumUsers sumPayedPremiumUsers sumBalanceSimpleApprovedUsers sumPayedSimpleApprovedUsers

3                      6                    35                            55

Advertisement

Answer

SELECT
  SUM(IF(isSimpleUser = 0, balance, 0)) AS sumBalancePremiumUsers,
  SUM(IF(isSimpleUser = 0, payed, 0)) AS sumPayedPremiumUsers,
  SUM(IF(isSimpleUser AND approvedAdminId IS NOT NULL, balance, 0)) AS sumBalanceSimpleApprovedUsers,
  SUM(IF(isSimpleUser AND approvedAdminId IS NOT NULL, payed, 0)) AS sumPayedSimpleApprovedUsers
FROM Users

Output:

+------------------------+----------------------+-------------------------------+-----------------------------+
| sumBalancePremiumUsers | sumPayedPremiumUsers | sumBalanceSimpleApprovedUsers | sumPayedSimpleApprovedUsers |
+------------------------+----------------------+-------------------------------+-----------------------------+
|                      3 |                    6 |                            35 |                          55 |
+------------------------+----------------------+-------------------------------+-----------------------------+
2 People found this is helpful
Advertisement