CODEWITHSUNDEEP
mysqlsql
Gelo
Gelo

Reputation: 105

SQL select all records only if all amount is not zero for a specific column

EMPLOYEE AMOUNT PAYCODE
121 0 SALARY
121 5 ALLOWANCE
122 0 SALARY
122 0 ALLOWANCE
123 5 SALARY
123 -5 ALLOWANCE

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

EMPLOYEE AMOUNT PAYCODE
121 0 SALARY
121 5 ALLOWANCE
123 5 SALARY
123 -5 ALLOWANCE

Upvotes: 0

Views: 1908

Answers (3)

SOS
SOS

Reputation: 6550

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:

Employee Amount PayCode
121 0 SALARY
121 5 ALLOWANCE
123 5 SALARY
123 -5 ALLOWANCE

db<>fiddle here

Upvotes: 1

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

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:

EMPLOYEE AMOUNT PAYCODE
121 0 SALARY
121 5 ALLOWANCE
123 5 SALARY
123 -5 ALLOWANCE

db<>fiddle here

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522171

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(*);

Upvotes: 3

Related Questions