0

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Michael Yoo
  • 49
  • 7
  • 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
  • Awesome thank you! – Michael Yoo Oct 18 '21 at 21:58
  • 1
    What happens if you have another row `a abc@gmail.com 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

1 Answers1

1
SELECT
ID,
count(distinct Email) as distinct_emails,
ProductName
FROM Products
group by ID,ProductName
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