Postgres combine 2 columns and use group by

Question

I have pat_first_name and pat_last_name columns in my psql database col. Id like to pick out most common names. I can do first names using

SELECT pat_first_name,count(pat_first_name) 
from patients 
GROUP BY pat_first_name 
ORDER BY count DESC;`

However, when I try combine it fails

SELECT pat_first_name,pat_last_name,count((pat_first_name || ' ' || pat_last_name)) 
from patients 
GROUP BY (pat_first_name || ' ' || pat_last_name) 
ORDER BY count DESC;

column "patients.pat_first_name" must appear in the GROUP BY clause or be used in an aggregate function

where am I going wrong?

Filipe Silva · Accepted Answer · 2013-12-06 16:37:41Z

3

You can just do:

SELECT pat_first_name,pat_last_name,COUNT(*) 
FROM patients 
GROUP BY pat_first_name,pat_last_name
ORDER BY COUNT(*) DESC;

Or, if you really want the first_name and last_name concatenated in the result:

SELECT pat_first_name || ' ' || pat_last_name,COUNT(*) 
FROM patients 
GROUP BY pat_first_name || ' ' || pat_last_name
ORDER BY COUNT(*) DESC;

Either way, you gotta have the same "non-count" columns in the SELECT as in the GROUP BY.

answered Dec 6, 2013 at 16:37

Filipe Silva

21.7k6 gold badges56 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres combine 2 columns and use group by

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related