Using DISTINCT and COUNT together in a MySQL Query
MysqlSqlMysql Problem Overview
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
Mysql Solutions
Solution 1 - Mysql
use
SELECT COUNT(DISTINCT productId) from table_name WHERE keyword='$keyword'
Solution 2 - Mysql
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 - Mysql
You were close :-)
select count(distinct productId) from table_name where keyword='$keyword'
Solution 4 - Mysql
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 - Mysql
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;
Solution 6 - Mysql
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: SELECTING DISTINCT PRODUCT AND DISPLAY COUNT PER PRODUCT
select * FROM Product
https://i.stack.imgur.com/hq0ua.png">
SELECT DISTINCT(Product_Name),
(SELECT COUNT(Product_Name)
from Product WHERE Product_Name = Prod.Product_Name)
as `Product_Count`
from Product as Prod
https://i.stack.imgur.com/DOSZp.png">
> Record Count: 4; Execution Time: 2ms
Solution 7 - Mysql
Isn't it better with a group by? Something like:
SELECT COUNT(*) FROM t1 GROUP BY keywork;