CODEWITHSUNDEEP
mysqlsqlcountdistinct
Sajith
Sajith

Reputation: 113

count of rows that do not contain a particular value in mysql

I have a table like this is mysql. I need to get the count of distinct doc_id that do not contain a particular attribute. For example, if the attribute is 'product', the result should be 1, ( ie., only the 4th doc_id does not contain product) 

+--------+-----------+--------+
| doc_id | attribute | value  |
+--------+-----------+--------+
|     1 | product   | mobile |
|     1 | model     | lumia  |
|     1 | camera    | 5mp    |
|     2 | product   | mobile |
|     2 | model     | lumia  |
|     2 | ram       | 1gb    |
|     3 | product   | mobile |
|     3 | year      | 2014   |
|     3 | made-in   | china  |
|     4 | brand     | apple  |
|     4 | model     | iphone |
|     4 | camera    | 5mp    |
|     5 | product   | camera |
|     5 | brand     | canon  |
|     5 | price     | 20000  |

Upvotes: 1

Views: 622

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can do this without a subquery using count(distinct):

select count(distinct doc_id) - count(distinct case when attribute = 'product' then doc_id end)
from table t;

With a subquery, you would first aggregate by doc_id and then do the count:

select count(*)
from (select doc_id, max(attribute = 'product') as has_product
      from table t
      group by doc_id
     ) t
where has_product = 0;

Upvotes: 3

Related Questions