Count including 0 on join two tables with different rows postgres

Question

I have two tables, users table that contains all the users in my system and other table called action with all the actions done by the users in my system, what I want to get is a query where i can count the actions by the user and if I don't have any action for one user get 0.

My two tables:

Users Table:

Name       Place
----------------------
James      Supermarket
Alex       Bank 
Ignatius   BookShop

Action table:

Name   Action
--------------------
James  Buy
James  Buy
Alex   Complaint
Alex   Buy
Alex   Buy

I want something like this:

Name       Transactions   Place
-------------------------------
James      2              Supermarket
Alex       3              Bank
Ignatius   0              BookShop

I tryied with:

SELECT users.name, 
   (SELECT Count(action.name) 
    FROM   action, 
           users 
    WHERE  action.name = users.name 
    GROUP  BY action.name) 
    users.place
FROM 
action, 
users 
WHERE  action.name = users.name

But is always giving me this error:

ERROR: more than one row returned by a subquery used as an expression SQL state: 21000

Any idea about how to do it?

Sergey Kalinichenko · Accepted Answer · 2017-12-11 12:16:29Z

3

You should do a left join first, then apply group by to its result:

SELECT
    u.name
,   COUNT(a.name) AS Transactions
,   u.place
FROM users u
LEFT OUTER JOIN action a ON a.name = u.name 
GROUP BY u.name, u.place

Left join ensures that a user record is not discarded even if it does not have associated transactions in the action table.

answered Dec 11, 2017 at 12:16

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

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

2 Comments

Joan Triay Over a year ago

hi, @dasblinkenlight it's working fine but I do not want include u.place in the group by, when I remove it is givinig me the error that say I have to include, I cannot include. How could I do it?

Sergey Kalinichenko Over a year ago

@JoanTriay Assuming that name uniquely identifies a user, it makes no difference that you use place in group by, because both fields come from the same table, and one of the fields (name) uniquely identifies the row.

Gordon Linoff · Accepted Answer · 2017-12-11 12:49:28Z

2

Never use commas in the FROM clause. Always use proper, explicit JOIN syntax. That said, you don't need any joins at all for this query, if you want to use correlated subqueries:

SELECT u.name, 
       (SELECT Count(*) 
        FROM action a 
        WHERE a.name = u.name 
       ) as cnt,
       u.place
FROM users u;

For performance, you want an index on action(name). With such an index, this may be faster than alternative versions using left join and group by.

Notes:

All the tables have table aliases, which are abbreviations for the table name.
No joins are needed for this version of the query.
No group by in the subquery.
No where clause is needed in the outer query.

edited Dec 11, 2017 at 12:49

answered Dec 11, 2017 at 12:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Joan Triay Over a year ago

Your solution works fine, slower, but it's good. thanks a lot

E.Abbott · Accepted Answer · 2017-12-11 12:45:16Z

1

As with previous answer a left join would see you through. If you want to include place in the results then do the left join after summarising

SELECT a.name,
        ISNULL(b.cnt, 0) AS Transactions,
        a.place
FROM users a
LEFT JOIN
   (SELECT name,
            Count(name) cnt
    FROM   action 
    GROUP  BY name) b
ON a.name =  b.name

answered Dec 11, 2017 at 12:45

E.Abbott

113 bronze badges

Collectives™ on Stack Overflow

Count including 0 on join two tables with different rows postgres

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related