Group By with inner join I am new to SQL world. I am practising on postgresql pgguide sample database . I wanted to count all the purchases of users who have bought a product 'Williams Selden'.  So i write something like below

select Count( p1.name) , u1.email from users as u1 INNER JOIN 
purchases as p1 ON u1.id=p1.user_id  
where u1.email IN (select u.email from users as u 
INNER JOIN purchases p ON u.id=p.user_id 
where p.name='Williams Selden') group by u1.email;


RESULT

 count |           email            
-------+----------------------------
    15 | Shanell.Maxson@gmail.com
    23 | Ozella.Yoshimura@gmail.com
    14 | Isabel.Breeding@gmail.com


Now i want to know what all they purchased. Something like below.



However i am not able to do that. Please help.
I wrote 

select DISTINCT p1.name , u1.email from users as u1 INNER JOIN 
purchases as p1 ON u1.id=p1.user_id  where u1.email 
IN (select u.email from users as u INNER JOIN purchases p ON 
u.id=p.user_id where p.name='Williams Selden') group by u1.email, 
p1.name;


As per first comment i removed the groupby and count and now i get something like. Maybe i am wrong at it, i do not want email with every record, i want it in rows just where  user email changes. Can it be done? If not, can i know why?

select p1.name , u1.email from users as u1 INNER JOIN purchases as p1 
ON u1.id=p1.user_id  
where u1.email IN (select u.email from users as u INNER JOIN 
purchases p ON u.id=p.user_id where p.name='Williams Selden') order by 
u1.email;

      name         |           email            
----------------------+----------------------------
 Pauletta Arends      | Isabel.Breeding@gmail.com
 Miyoko Potter        | Isabel.Breeding@gmail.com
 Wendie Westmoreland  | Isabel.Breeding@gmail.com
 Williams Selden      | Isabel.Breeding@gmail.com
 Jettie Dilks         | Isabel.Breeding@gmail.com
 Catina Kiser         | Isabel.Breeding@gmail.com
 Bradly Vasko         | Isabel.Breeding@gmail.com
 Inocencia Cousineau  | Ozella.Yoshimura@gmail.com
 Cammy Bough          | Ozella.Yoshimura@gmail.com
 Danyelle Haefner     | Ozella.Yoshimura@gmail.com
 Ricarda Goldsberry   | Ozella.Yoshimura@gmail.com
 Cherryl Arends       | Ozella.Yoshimura@gmail.com
 Susanna Melendez     | Ozella.Yoshimura@gmail.com
 Granville Junge      | Ozella.Yoshimura@gmail.com
 Jerald Schuh         | Ozella.Yoshimura@gmail.com
 Miyoko Allbright     | Shanell.Maxson@gmail.com
 Tommie Ange          | Shanell.Maxson@gmail.com
 Williams Selden      | Shanell.Maxson@gmail.com
 Isabel Tripodi       | Shanell.Maxson@gmail.com
I would not apply formatting directly in the query, but rather at the application layer.

If you really want to, you could do it like this:

WITH CTE
AS
( 
SELECT  p1.name, 
        u1.email,
        ROW_NUMBER() OVER(PARTITION BY Email) as rownum,
        p1.user_id
FROM users as u1 
INNER JOIN purchases as p1 
ON u1.id=p1.user_id  
WHERE u1.email IN (SELECT u.email 
                   FROM users as u 
                   INNER JOIN purchases p 
                   ON u.id=p.user_id 
                   WHERE p.name='Williams Selden') 
)
SELECT
name,
CASE WHEN rownum = 1 THEN email
ELSE ''
END AS email
FROM CTE
ORDER BY user_id;


DB-Fiddle

But I would change the IN to EXISTS, due to how IN handles NULL values (and remove the extra inner join)

WITH CTE
AS
( 
SELECT  p1.name, 
        u1.email,
        ROW_NUMBER() OVER(PARTITION BY Email) as rownum,
        p1.user_id
FROM users as u1 
INNER JOIN purchases as p1 
ON u1.id=p1.user_id  
WHERE EXISTS  (SELECT u1.email 
               FROM  purchases p 
               WHERE u1.id= p.user_id 
               AND p.name='Williams Selden') 
)
SELECT
name,
CASE WHEN rownum = 1 THEN email
ELSE ''
END AS email
FROM CTE
ORDER BY user_id;


DB-Fiddle

Result for both

| name                | email                      |
| ------------------- | -------------------------- |
| Pauletta Arends     | Isabel.Breeding@gmail.com  |
| Miyoko Potter       |                            |
| Wendie Westmoreland |                            |
| Williams Selden     |                            |
| Jettie Dilks        |                            |
| Catina Kiser        |                            |
| Bradly Vasko        |                            |
| Inocencia Cousineau | Ozella.Yoshimura@gmail.com |
| Cammy Bough         |                            |
| Williams Selden     |                            |