CODEWITHSUNDEEP
mysqlsql
Ali
Ali

Reputation: 267077

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

Upvotes: 143

Views: 247370

Answers (7)

Rhalp Darren Cabrera
Rhalp Darren Cabrera

Reputation: 966

SELECTING DISTINCT PRODUCT AND DISPLAY COUNT PER PRODUCT

for another answer about this type of question, this is my way of getting the count of product based on their product name using distinct. Here a sample:

List of Product (SQLFiddle):

select * FROM Product

Product Name Count Using Distinct (SQLFiddle):

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

Optimized version without Distinct:

SELECT Product_Name, COUNT(Product_Name) AS `Product_Count`
FROM Product
GROUP BY Product_Name

Upvotes: 7

tekBlues
tekBlues

Reputation: 5793

You were close :-)

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

Upvotes: 43

David
David

Reputation: 5456

use 

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

Upvotes: 313

Gratzy
Gratzy

Reputation: 9389

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.

Upvotes: 74

George SEDRA
George SEDRA

Reputation: 786

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;

Upvotes: 9

Alistair Hart
Alistair Hart

Reputation: 277

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'

Upvotes: 26

Macarse
Macarse

Reputation: 93143

Isn't it better with a group by? Something like:

SELECT COUNT(*) FROM t1 GROUP BY keywork;

Upvotes: -6

Related Questions