CODEWITHSUNDEEP
sqlpostgresqllaravel-query-builder
Auxulry
Auxulry

Reputation: 193

Select Data with Count in Postgresql

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.

Upvotes: 1

Views: 8091

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269913

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.

Upvotes: 0

rohitvats
rohitvats

Reputation: 1851

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"

Upvotes: 2

Ravi Makwana
Ravi Makwana

Reputation: 2918

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"

Upvotes: 0

Related Questions