Warning: Null value is eliminated by an aggregate or other SET operation.
You might see this when you execute some SQL against MS SQL Server.
Simple answer, you are doing something across multiple rows where the column you are using contains NULLs so the result might not be what you expect. Use COALESCE to explicitly state what you want to happen.
Longer answer, take the following set of data:
Which is taken from a user account table. You can see a rowid which is the primary key and then two columns with the datetime of the last login and the number of logins. Now suppose you want to average the number of logins per user. You write the following:
SELECT AVG(nologins) from userdata
And when you run this, you get the warning above. This makes complete sense. When you are averaging NULL, what are you expecting? We might assume NULL is like zero, but of course it isn't necessarily - it literally means the value is undefined. What actually happens is that the rows with a NULL value are EXCLUDED from the function. This might not be too bad for a SUM operation, the results are probably what you would expect but in the case of AVG, because AVG uses the total number of rows in the calculation, if some rows are excluded, the AVG results will differ.
In my case, if the nologins is NULL, it means the user has never logged in and therefore it is equivalent to zero (and I could have used that as the default!) but in this case, I need to modify my select to be the following:
SELECT AVG(COALESCE(nologins,0)) from userdata
Which tells SQL Server that I want a zero when it finds a NULL rather than an excluded row. In the small set of data I have, if I ignore the warning, the first select statement returns 6, whereas the second returns 5!
You might not even see the warning sometimes, so just take a little extra time when using aggregate operations to think about what you want the result to do.
Simple answer, you are doing something across multiple rows where the column you are using contains NULLs so the result might not be what you expect. Use COALESCE to explicitly state what you want to happen.
Longer answer, take the following set of data:
Which is taken from a user account table. You can see a rowid which is the primary key and then two columns with the datetime of the last login and the number of logins. Now suppose you want to average the number of logins per user. You write the following:
SELECT AVG(nologins) from userdata
And when you run this, you get the warning above. This makes complete sense. When you are averaging NULL, what are you expecting? We might assume NULL is like zero, but of course it isn't necessarily - it literally means the value is undefined. What actually happens is that the rows with a NULL value are EXCLUDED from the function. This might not be too bad for a SUM operation, the results are probably what you would expect but in the case of AVG, because AVG uses the total number of rows in the calculation, if some rows are excluded, the AVG results will differ.
In my case, if the nologins is NULL, it means the user has never logged in and therefore it is equivalent to zero (and I could have used that as the default!) but in this case, I need to modify my select to be the following:
SELECT AVG(COALESCE(nologins,0)) from userdata
Which tells SQL Server that I want a zero when it finds a NULL rather than an excluded row. In the small set of data I have, if I ignore the warning, the first select statement returns 6, whereas the second returns 5!
You might not even see the warning sometimes, so just take a little extra time when using aggregate operations to think about what you want the result to do.