I am stuck with this problem. I want to select only the employees where all amounts are zero(0). Tried using HAVING SUM(Amount) but will need to also select records like the employee 123 above. The output must be:

CodePudding user response：

You were on the right track. Consider this version:

SELECT EMPLOYEE
FROM yourTable
GROUP BY EMPLOYEE
HAVING SUM(AMOUNT <> 0) = 0;

You could also phrase this by saying that the zero amount count is equal to the total count:

SELECT EMPLOYEE
FROM yourTable
GROUP BY EMPLOYEE
HAVING SUM(AMOUNT = 0) = COUNT(*);

CodePudding user response：

You can use exists. First, I have selected all the employees for which there is at least a single row in which amount is not 0. Then with exists() I have selected all the rows for those employees.

Schema and insert statements:

 create table employee_payment(EMPLOYEE int, AMOUNT int, PAYCODE varchar(50));
 insert into employee_payment values(121,   0,  'SALARY');
 insert into employee_payment values(121,   5,  'ALLOWANCE');
 insert into employee_payment values(122,   0,  'SALARY');
 insert into employee_payment values(122,   0,  'ALLOWANCE');
 insert into employee_payment values(123,   5,  'SALARY');
 insert into employee_payment values(123,   -5, 'ALLOWANCE');

Query:

 select * from employee_payment e
 where exists 
 (
   SELECT 1
   FROM employee_payment ep
   where ep.amount<>0 and e.EMPLOYEE=ep.EMPLOYEE
 )

Output:

db<>fiddle here

CodePudding user response：

Use a conditional SUM() and window functions to get the total non-zero amounts per employee. Then exclude ones with all 0's

 WITH cte AS (
   SELECT * 
          , SUM(CASE WHEN Amount > 0 THEN 1 ELSE 0 END) OVER(PARTITION BY Employee) AS TotalNonZero
   FROM   Employee
 )
 SELECT Employee, Amount, PayCode
 FROM   cte
 WHERE  TotalNonZero > 0
 ;

Results:

db<>fiddle here