CODEWITHSUNDEEP
sqlpostgresql
moadeep
moadeep

Reputation: 4098

Postgres combine 2 columns and use group by

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?

Upvotes: 0

Views: 2248

Answers (1)

Filipe Silva
Filipe Silva

Reputation: 21657

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.

Upvotes: 3

Related Questions