CODEWITHSUNDEEP
mysqlsql
user3785612
user3785612

Reputation: 93

Query to calculate the number of different products sold to a certain customer

Say there is an SQL table 

SHIPMENTS(ProductNumber, ClientNumber, Quantity)
(1,1,4)
(2,5,2)
(1,1,2)
(4,1,5)
(2,5,3)

In the example above we notice that client #1 has made three purchases (twice the same product). Hence, the answer should be 2 since we're looking for the number of DIFFERENT products purchased by that client.

Applying this query

SELECT count(*)
FROM SHIPMENTS
WHERE ClientNumber = 1;

Will understandably give 3 as a result. And I can't think of the solution as to how to calculate only for the different products.

Upvotes: 2

Views: 390

Answers (1)

Jester
Jester

Reputation: 1408

SELECT count(DISTINCT ProductNumber)
FROM SHIPMENTS
WHERE ClientNumber = 1;

This should do the trick. Adding DISTINCT takes every unique value once: http://www.w3schools.com/sql/sql_distinct.asp

Upvotes: 3

Related Questions