Using DISTINCT and COUNT together in a MySQL Query

Question

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

Answer 1

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.

Answer 2

You were close :-)

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

Answer 3

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'

Answer 4

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; 

Answer 5

SELECTING DISTINCT PRODUCT AND DISPLAY COUNT PER PRODUCT

for another answer about this type of question this is my another answer for getting count of product base on product name distinct like this sample below:

Table Value

select * FROM Product

Counted Product Name

SELECT DISTINCT(Product_Name),
(SELECT COUNT(Product_Name) 
from Product  WHERE Product_Name = Prod.Product_Name)  
as `Product_Count`
from Product as Prod

Record Count: 4; Execution Time: 2ms