When I run this query It works
SELECT sum( amount ) AS balance FROM balance WHERE amount >= 100
but when I want to filter for the userid it returns NULL
SELECT sum( amount ) AS balance FROM balance WHERE amount >= 100 AND userid=4
views:
11answers:
1
+2
A:
It will return NULL if there are no rows. If you want zero instead then use this:
SELECT IFNULL(SUM(amount), 0) AS balance
FROM balance
WHERE amount >= 100 AND userid = 4
If you believe that the answer should be something other than 0 or NULL then I suggest you run this query to double-check that at least one row is returned and that the data is correct:
SELECT *
FROM balance
WHERE amount >= 100 AND userid = 4
Mark Byers
2010-08-29 10:14:15
it doesn't matter if it returns NULL or 0. I've run the query but changed 100 to 20 and it returns the rows as expected.
krike
2010-08-29 10:30:18
I found the problem, i make the sum of amount but then I only check the individual amount if it's greater or equal to 100. So I changed the where to the following and it seems to work: WHERE userid = 4 GROUP BY userid having sum(amount) >= 100
krike
2010-08-29 10:45:23
you helped me on the right path for the answer so I marked it as answered :)
krike
2010-08-29 10:46:23