Using DISTINCT and COUNT together in a MySQL Query

Is something like this possible:

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

What I want is to get the number of unique product Ids which are associated with a keyword. The same product may be associated twice with a keyword, or more, but i would like only 1 time to be counted per product ID

Solution 1:

use

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

Solution 2:

I would do something like this:

Select count(*), productid
from products
where keyword = '$keyword'
group by productid

that will give you a list like

count(*)    productid  
----------------------
 5           12345   
 3           93884   
 9           93493    

This allows you to see how many of each distinct productid ID is associated with the keyword.

Solution 3:

You were close :-)

select count(distinct productId) from table_name where keyword='$keyword'

Solution 4:

FYI, this is probably faster,

SELECT count(1) FROM (SELECT distinct productId WHERE keyword = '$keyword') temp

than this,

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

Solution 5:

What the hell of all this work anthers

it's too simple

if you want a list of how much productId in each keyword here it's the code

SELECT count(productId),  keyword  FROM `Table_name` GROUP BY keyword;