subquery double where aggreate functions-CodePudding

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

CodePudding user response：

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

CodePudding user response：

You just need to Sum these with case when then clause as per your condition

SELECT
  SUM(CASE WHEN isSimpleUser = 0 then balance else 0 end) AS sumBalancePremiumUsers,
  SUM(CASE WHEN isSimpleUser = 0 then payed else 0 end) AS sumPayedPremiumUsers,
  SUM(CASE WHEN isSimpleUser =1 AND approvedAdminId IS NOT NULL then balance else 0 end ) AS sumBalanceSimpleApprovedUsers,
  SUM(CASE WHEN isSimpleUser=1 AND approvedAdminId IS NOT NULL then  payed else  0 end) AS sumPayedSimpleApprovedUsers
FROM Users

Check this out - <>db-fiddle