CODEWITHSUNDEEP
mysql
Atiq
Atiq

Reputation: 488

How to select those distinct rows, in which non of value is non positive

I am using MySQL.
Let's take an example.
table_a

number   value
  1        1
  1        1
  1        0
  2        1
  2        1
  3        1
  3        0

I want all distinct rows which do not have any value 0 in it, as in the above case expected result is 2. As we know MySQL doesn't support minus operator, can somebody help me out with the query.

Edit: Answer should be 2 by mistake i changed it to 1(edited).

Upvotes: 1

Views: 485

Answers (4)

Vipin Jain
Vipin Jain

Reputation: 3756

Try the following:

SELECT
number,
DISTINCT(value)
FROM table_a
WHERE value > 0;

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

Try this:

SELECT number  
FROM mytable
GROUP BY number 
HAVING COUNT(CASE WHEN value = 0 THEN 1 END) = 0

HAVING clause filters out number slices containing at least one 0 value.

Demo here

Or, alternatively: 

SELECT number  
FROM mytable
GROUP BY number 
HAVING MIN(value) > 0

The above query selects number groups containing only positive, non-zero, values.

Demo here

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522211

Here is an alternative to using the DISTINCT keyword:

SELECT number, value
FROM table_a
GROUP BY number, value
HAVING SUM(value) > 0

If you want the number of records where both the number and value equal 1, then I would try the following:

SELECT COUNT(*)
FROM table_a
WHERE number = 1 AND value = 1

Upvotes: 1

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

Both column Distinct like this

SELECT DISTINCT number, value FROM table_a
WHERE value <> 0;

Upvotes: 0

Related Questions