Last week, I was working on some data validation activity with one of the developers from my company. While working on data validation, we had one surprise and learning also. Maybe you already know this, but if not, I would like to share this with you.
To make it easy to understand you, I am explaining scenario with rounded-off values. So have one table, say test with a total of 1000 rows in it. In that we have a one column named ID and allowed for NULL value. In the table test, 400 rows have ID=0, 400 rows have ID=1 and 200 rows have ID value NULL.
Now when we executed
select count (1) from
test where ID=1
It gave a output 400
but when we executed
select count (1) from
test where ID
<>1
We were expecting output 600, but it gave 400, this was surprise for us.
By using group by on ID column I checked count for each value of ID and when did math I got to know < > 1 only considers 0 and not NULL.
So solution for this is you should use
select count (1) from
test where
(ID =0 or ID
is NULL)
Let me show this in actual.
1st I am doing select all for reference. we have (0,1,NULL) in ID column.
No comments:
Post a Comment