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.

When several tables are involved, it's good programming practice to qualify all columns, like "transaction"."user_id". — jarlh
– jarlh, Commented Jan 22, 2020 at 8:49
You typically GROUP BY the same columns as you SELECT, except those who are arguments to set functions. — jarlh
– jarlh, Commented Jan 22, 2020 at 8:49
I don't think there is a need for group by to call others. Thank you very helpful — Auxulry
– Auxulry, Commented Jan 22, 2020 at 8:55

rohitvats · Accepted Answer · 2020-01-22 08:58:03Z

2

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"

edited Jan 22, 2020 at 8:58

answered Jan 22, 2020 at 8:49

rohitvats

1,85113 silver badges14 bronze badges

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

1 Comment

Auxulry Over a year ago

Thank you for the explanation now I am understand, this works now !. :)

Ravi Makwana · Accepted Answer · 2020-01-22 08:54:11Z

0

When every your using GROUP BY You must be group by all column which is fetching or use Aggregate Functions

Group by (same as @rohitvats)

GROUP BY "transaction"."user_id", "user"."name"

---- OR ----

Aggregate Functions MAX(), MIN()

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

answered Jan 22, 2020 at 8:54

Ravi Makwana

2,9201 gold badge32 silver badges42 bronze badges

1 Comment

Auxulry Over a year ago

This is very helpful, after this i will learn more about the function you mean.

Gordon Linoff · Accepted Answer · 2020-01-22 11:50:57Z

0

Your code would work if you aggregated by the users.id:

SELECT u.id, u.user_name, COUNT(*) as trans_count
FROM users u JOIN
     transaction t
     ON t.id = u.user_id
GROUP BY u.id;

(I removed the double quotes because they clutter the logic and are not necessary to explain what is going on.)

Why? Presumably, users.id is unique (or equivalently the primary key). Postgres supports aggregating by a unique key in a table and also including unaggregated columns in the SELECT. This is an implementation of "functional dependent" aggregations from the SQL standard.

When you use transactions.user_id, Postgres does not recognize the functional dependence (even though you might think that the ON clause would imply it). So, your code doesn't work.

The alternative is to add user_name to the GROUP BY as well. However, your version almost works if you use the column from the correct table.

answered Jan 22, 2020 at 11:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Auxulry Over a year ago

So like that. Thank you for your explanation is very easy to understand. :)

Collectives™ on Stack Overflow

Select Data with Count in Postgresql

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related