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
Isn't it better with a group by? Something like:
SELECT COUNT(*) FROM t1 GROUP BY keywork;
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;
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:
select * FROM Product
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
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'
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.
You were close :-)
select count(distinct productId) from table_name where keyword='$keyword'
Source: Stackoverflow.com