Take the example table in SQL Server
col1, col2, col3, col4
id1, 0.00, 0.00, 0.00
id2, 0.00, 1.00, 0.00
id3, 5.55, 2.22, 0.00
id4, 0.00, 0.00, 0.00
id5, 1.11, 2.22, -3.33
I'd like to implement a WHERE
clause so that when all the values in col2, col3 and col4 equal zero that the line is excluded from the results.
I've tried putting the following WHERE
clause where col2!=0 and col3!=0 and col4!=0
This returns only the id5 row, when what I'm after is to return id2, id3 and id5.
I know the where clause is wrong, but not sure what other things to try out.
I've thought about doing a sum across the columns but this isn't desirable, as the decimals can go in either direction and might on the off chance equal zero even when all the values are populated
The SQL Not Equal comparison operator (!=) is used to compare two expressions. For example, 15 !=
Both are valid, but '<>' is the SQL-92 standard.
Difference between SQL Not Equal Operator <> and != to do inequality test between two expressions. Both operators give the same output. The only difference is that '<>' is in line with the ISO standard while '!= ' does not follow ISO standard.
But the WHERE.. IN clause allows only 1 column.
What you intended is:
where not (col2=0 and col3=0 and col4=0)
interprets as all rows that don't have all three zero.
or
where col2!=0 or col3!=0 or col4!=0
which interprets as all rows which have at least one of the columns non zero.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With