SQL Query with Distinct and Count-CodePudding

I want to know how to query a table with both distinct and count feature.

For example:

SELECT ID, Email, ProductName, ProductModel
FROM Products

What can I do to pull data with Distinct feature on ID and per ID, Count of Email?

From something like:

    ID  email       ProductName 
0   a   abc@gmail.com   apple       
1   b   bcd@gmail.com   orange
2   a   cde@gmail.com   apple       
3   b   def@gmail.com   orange
4   c   efg@gmail.com   grapefruit
5   a   fgh@gmail.com   apple       
6   b   ghi@gmail.com   orange
7   c   hij@gmail.com   grapefruit
8   a   ijk@gmail.com   apple       
9   a   jkl@gmail.com   apple       
10  a   klm@gmail.com   apple

To something like:

    ID  Count       ProductName 
0   a   6       apple       
1   b   3       orange
2   c   2       grapefruit

Any help would be appreciated.

CodePudding user response：

SELECT
ID,
count(distinct Email) as distinct_emails,
ProductName
FROM Products
group by ID,ProductName