CODEWITHSUNDEEP
mysqlsql
Chris
Chris

Reputation: 805

Make select sum() not return null

I need to make a sql query with sum that will show me only records where sum is not null and != 0. Now I have:

QUERY

SELECT SUM(time) AS sumToPay,
w.name, w.lname 
FROM `service` s 
JOIN worker w 
ON s.worker_id = w.id 
GROUP BY s.worker_id

It shows me proper sum of time but when one worker has no time it shows me 0. How to force query to show only not null values?

Upvotes: 1

Views: 118

Answers (2)

asontu
asontu

Reputation: 4639

It's not entirely clear to me whether you wish to completely omit rows where the SUM() comes out as 0 or whether you want to see the SUM() of all none-null values. Donal's answer gives you the former, this should give you the latter:

SELECT SUM(time) AS sumToPay,
w.name, w.lname 
FROM `service` s 
JOIN worker w ON s.worker_id = w.id 
WHERE time IS NOT NULL
GROUP BY s.worker_id

Upvotes: 0

Donal
Donal

Reputation: 32713

You could use a having clause

SELECT 
sum(time) as sumToPay, 
w.name, 
w.lname 
FROM `service` s 
join worker w on s.worker_id = w.id 
group by s.worker_id 
having sum(time) > 0

Upvotes: 5

Related Questions