subquery double where + aggreate functions

December 9, 2021

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

>Solution :

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