Mysql distinct with count with condition

Question

SELECT COUNT(DISTINCT productId) WHERE keyword='$keyword'

How can I get count of distinct row by condition

SELECT COUNT(DISTINCT productId -- THIS IS WHERE I NEED TO PUT CONDITION --) WHERE keyword='$keyword'

I need to cont distinct rows if that row doesn't have type <> 1

If you need distinct productId and multiple exist - which row to take - it can be important because rows having the same productid can have different types values — juergen d
– juergen d, Commented Sep 8, 2014 at 10:08
Does the condition necessarily have to to be inside the COUNT function? — Salman Arshad
– Salman Arshad, Commented Sep 8, 2014 at 10:11
I want all records to be selected, because I'm doing calculation on them, I just don't want count of products if they have type 1 — Developerium
– Developerium, Commented Sep 8, 2014 at 10:12

Rahul Tripathi · Accepted Answer · 2014-09-08 10:14:44Z

2

I am not sure but are you looking for this:

SELECT COUNT(DISTINCT productId) from table_name WHERE keyword='$keyword' and type!=1

EDIT:

SELECT COUNT(DISTINCT CASE WHEN type <> 1 
                           THEN productId 
                      ELSE NULL 
                      END
             ) 
FROM table_name
WHERE keyword='$keyword'

edited Sep 8, 2014 at 10:14

answered Sep 8, 2014 at 10:08

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Developerium Over a year ago

no I want all records to be selected, because I'm doing calculation on them, I just don't want count of prodcuts if they have type 1

Rahul Tripathi Over a year ago

@tinybyte:- You may use the CASE then. See the update

Salman Arshad · Accepted Answer · 2014-09-08 10:19:14Z

1

Assuming there is an issue with modifying the WHERE clause, you can use CASE operator or IF function:

SELECT COUNT(DISTINCT CASE WHEN type <> 1 THEN productId ELSE NULL END) 
FROM <table>
WHERE keyword = '<keyword>'

edited Sep 8, 2014 at 10:19

answered Sep 8, 2014 at 10:13

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Comments

Barmar · Accepted Answer · 2014-09-08 10:09:03Z

0

The extra condition goes in the WHERE clause, not in the SELECT clause.

SELECT COUNT(DISTINCT productId)
WHERE keyword = '$keyword'
AND type != 1

answered Sep 8, 2014 at 10:09

Barmar

789k57 gold badges555 silver badges669 bronze badges

Collectives™ on Stack Overflow

Mysql distinct with count with condition

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related