CODEWITHSUNDEEP
sqlsql-server-2008-r2filtering
olivier
olivier

Reputation: 45

SQL Server Display only lines with all values not equal to 0

In SQL server 2008r2, i want to display only lines where some columns are not equal to '0'

For example: I want to display only lines where columns 2 and 3 are <> '0'

line 1 : val1 = 2, val2 = 2, val3 = 0

line 2 : val1 = 1, val2 = 0, val3 = 0

line 3 : val1 = 0, val2 = 0, val3 = 5

line 4 : val1 = 10, val2 = 0, val3 = 3

line 5 : val1 = 0, val2 = 0, val3 = 0

In this example, i want to display lines 1,3,4 but not lines 2 and 5 because both columns 2 & 3 are = '0'

I tried with NOT EXISTS but it didn't work.

EDIT : Hi, i think it's difficult for me to explain clearly what i want, i put you my definitive request, perhaps you could define my need ^^ :

select * from CONSULTANT inner join REPORTINGCONSULTANT on CONSULTANT.ID = REPORTINGCONSULTANT.FK_CONSULTANT where [CONSULTANT].[ISDESACTIVE] = '0' and [CONSULTANT].[ISSUPPRIME] = '0' and [CONSULTANT].[INITIALES] not like 'IL%' and [REPORTINGCONSULTANT].[DATEDEBUT] >= '02/06/2014' and [REPORTINGCONSULTANT].[NBCANDIDATSPUSH] <> '0' and [REPORTINGCONSULTANT].[NBCVENVOYESURPOSTE] <> '0' and [REPORTINGCONSULTANT].[NBRDVPROSPECTS] <> '0' and [REPORTINGCONSULTANT].[NBRDVCLIENTS] <> '0' and [REPORTINGCONSULTANT].NBPROSPECTSRENCONTRES] <> '0' and [REPORTINGCONSULTANT].[NBPROSPECTSRENCONTRESBINOME] <> '0' and [REPORTINGCONSULTANT].[NBCLIENTSRENCONTRES] <> '0' and [REPORTINGCONSULTANT].[NBCLIENTSRENCONTRESBINOME] <> '0'

When i run only the begining (stop after the date), it counts me 1700 results If i run the rest, it counts me 0 result

but i can find lines where all lines are true

Upvotes: 0

Views: 3557

Answers (4)

Jaugar Chang
Jaugar Chang

Reputation: 3196

Count the number of values not equal to 0 by sign(abs(x)).

SELECT t.* 
FROM dbo.TableName t
WHERE sign(abs(val1))+sign(abs(val2))+sign(abs(val3)) >1

It implements the requirement of :

display only lines where some columns are not equal to '0'

If "some" means val2 and val3, it could be:

SELECT t.* 
FROM dbo.TableName t
WHERE sign(abs(val2))+sign(abs(val3)) >0

Upvotes: 0

AHiggins
AHiggins

Reputation: 7219

Adding to Tim Schmelter's probably-correct answer, you might also be asking for the HAVING clause.

The WHERE statement he used is for when the values you're trying to filter are the actual values in a table, or the result of calculations. That's probably what you're looking for, though your question is so vague it's hard to tell.

However, sometimes people have a hard time filtering out the result of an aggregation that has been performed on the table, such as when you are summing up some values and want to exclude those with a sum equal to that scenario. So, if you had the following query:

SELECT SUM(someValue) AS val1, MAX(someOtherValue) AS val2, AVG(yetAnotherValue) AS val3
FROM someTable
GROUP BY someThing

you could filter out places where val2 and val3 are both zero like this:

SELECT SUM(someValue) AS val1, MAX(someOtherValue) AS val2, AVG(yetAnotherValue) AS val3
FROM someTable
GROUP BY someThing
HAVING MAX(someOtherValue) != 0 AND AVG(yetAnotherValue) != 0

Upvotes: 0

Jeffrey Wieder
Jeffrey Wieder

Reputation: 2374

It seems like you are asking to get all rows where val2 <> 0 OR val3 <> 0 based on the result set you are expecting.

SELECT TABLE.*  FROM TABLE WHERE val2 <> 0 OR val3 <> 0

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460228

i want to display only lines where columns 2 and 3 are <> '0'

I don't see the particular problem

SELECT t.* 
FROM dbo.TableName t
WHERE t.val2 <> 0 AND t.val3 <> 0

Upvotes: 1

Related Questions