CODEWITHSUNDEEP
sqlcountaggregate-functions
Michael Xia
Michael Xia

Reputation: 458

How to find the number of distinct values in a column with SQL

Suppose I have 3 tables:

customer_table(customer_id, customer_name)

product_table(product_id, product_name)

purchase_table(product_id, customer_id)

And I want to find the customer_id and the number of different types of products purchased by the this customer.

This is what I have so far but it's incorrect

select c.customer_id
    from customer_table c;
union
select count(distinct product_id)
    from purchase_table
        where customer_id = c.customer id;

Upvotes: 0

Views: 43

Answers (1)

nbk
nbk

Reputation: 49375

You can use a correlated subquery:

select c.customer_id,
    (select count(distinct product_id)
         from purchase_table
             where customer_id = c.customer id) as count_product
    from customer_table c;

Upvotes: 1

Related Questions