Select Data with Count in Postgresql

Question

I want to ask how do I select data together with count data.

In this case, I want the user to appear and the number of transactions that he has.

Like this code that I made.

SELECT "transaction"."user_id",
COUNT(transaction.id) trans_count
FROM transaction 
inner join "users" on "users"."id" = "transaction"."user_id"
GROUP BY user_id

The code above successfully selects user_id and trans_count, but when I am trying to show users.name

this error message appears.

Error in query: ERROR: column "users.name" must appear in the GROUP BY clause or be used in an aggregate function LINE 3: "users"."name"

Is it true that I am cannot select other data when I count data or is there a better way ?.

Thank You.

Answer 1

You can include user.name in the group by:

SELECT "transaction"."user_id",
"user"."name",
COUNT(transaction.id) trans_count
FROM transaction 
inner join "users" on "users"."id" = "transaction"."user_id"
GROUP BY "transaction"."user_id", "user"."name"

Otherwise, when the DBMS tries to combine (group) multiple rows into a single row, it doesn't know which name value should it pick, which is why it throws the error.

In this case, user_id and user.name have a one-to-one mapping, so you can simply include name in the group by clause.

Otherwise you'd have to tell the DBMS how to select one value from the multiple records that are in each group, eg:

min(user.name) or max(user.name)

SELECT "transaction"."user_id",
min("user"."name") user_name,
COUNT(transaction.id) trans_count
FROM transaction 
inner join "users" on "users"."id" = "transaction"."user_id"
GROUP BY "transaction"."user_id"