SQL Query with Distinct and Count

Question

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   [email protected]   apple       
1   b   [email protected]   orange
2   a   [email protected]   apple       
3   b   [email protected]   orange
4   c   [email protected]   grapefruit
5   a   [email protected]   apple       
6   b   [email protected]   orange
7   c   [email protected]   grapefruit
8   a   [email protected]   apple       
9   a   [email protected]   apple       
10  a   [email protected]   apple

To something like:

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

Any help would be appreciated.

Please provide a [mre] with sample data and desired results. — Dale K, Oct 18 '21 at 21:30
duplicate https://stackoverflow.com/questions/1521605/selecting-count-with-distinct — Nonik, Oct 18 '21 at 21:32
Oh sorry, pretty new to this website didn't know I had to accept answers. I'll go back and do that now. @DaleK how do you accept an answer..? — Michael Yoo, Oct 18 '21 at 21:45
What happens if you have another row `a [email protected] orange` how do you want that to show In other words, what happens if you have multiple `ProductName` per `ID` — Charlieface, Oct 18 '21 at 22:50
Oh hmm, so for the email column - there should not be any duplicate and the product name column should correspond to the key id... so if your proposed case do occur... that would be a nightmare case... haha — Michael Yoo, Oct 19 '21 at 00:19

score 1 · Accepted Answer · answered Oct 18 '21 at 22:52

1

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

answered Oct 18 '21 at 22:52

barker

1,005
18
36

Hey Barker, when the ID column is numeric, this code returns error: 'is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.' – Michael Yoo Oct 19 '21 at 02:34
did you add "product model" to your select statement and not in your group by statement? if so, see here https://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e – barker Oct 19 '21 at 14:52
Aaah got it, thank you so much for the help. – Michael Yoo Oct 19 '21 at 15:37

SQL Query with Distinct and Count

1 Answers1