select name and email and count using subquery in postgresql

Question

I'm trying write a Postgres query to display a distinct number of people but count the actual (non-distinct) number of people.

So if I have

1  Ray [email protected]
2  Ray [email protected]
3  Kate [email protected]

I'd want to show:

Ray 2
Kate 1

==

SELECT name, email, COUNT(*) 
FROM (SELECT DISTINCT name, email 
FROM people
WHERE degree = 'Gradiate')

I get:

ERROR:  subquery in FROM must have an alias
LINE 3: FROM (SELECT DISTINCT name, email

How to fix this?

possible duplicate of [subquery in FROM must have an alias](http://stackoverflow.com/questions/14767209/subquery-in-from-must-have-an-alias) — Ram, Oct 15 '14 at 23:01

score 3 · Answer 1 · edited May 23 '17 at 12:27

3

You do not need the subquery at all (which would require a table alias as has been pointed out). It wouldn't make sense for what you need.

Use GROUP BY like this:

SELECT name, email, count(*) AS ct
FROM   people
WHERE  degree = 'Graduate'
GROUP  BY 1, 2;

GROUP BY 1, 2 is shorthand for GROUP BY name, email in this case. Example with more details.

Of course, name and email have to match here, unlike your example where you only display name and count (which contradicts your query).

edited May 23 '17 at 12:27

Community

answered Oct 15 '14 at 23:28

1

@Nona: `Ray [email protected]` and `Ray [email protected]` and `Ray NULL` (`NULL` value for email) will all be listed *separately* in the result (same name but different email). – Erwin Brandstetter Oct 15 '14 at 23:35

score 0 · Answer 2 · answered Oct 15 '14 at 22:57

0

Add an

AS some_alias

Like:

SELECT name, email, COUNT(*) 
FROM (SELECT DISTINCT name, email 
FROM people
WHERE degree = 'Gradiate') as my_alias

answered Oct 15 '14 at 22:57

ErlVolton

I get: ERROR: column "my_aliasname" must appear in the GROUP BY clause or be used in an aggregate function LINE 2: SELECT name, email, COUNT(*) – Nona Oct 15 '14 at 23:06
Sounds like a typo. Can we see the exact query you're running after adding the alias? – ErlVolton Oct 15 '14 at 23:10
It should read "my_alias.name" in my comment above. Your query is what I'm running (assuming I understood you correctly) – Nona Oct 15 '14 at 23:22

score 0 · Answer 3 · answered Oct 16 '14 at 13:35

0

SELECT name, count(*) AS count
FROM   people
WHERE  degree = 'Gradiate'
GROUP  BY name;

answered Oct 16 '14 at 13:35

Ben

3 Answers3