CODEWITHSUNDEEP
sqlpostgresqljoin
user70192
user70192

Reputation: 14204

Mixing INs and Cross Table queries in Postgres

I'm new to Postgres. I have a query that involves 4 tables. My tables look like the following:

User            Account             Transaction         Action
----            -------             -----------         ------
ID              ID                  ID                  ID
Name            UserID              AccountID           AccountID
                                    Description         Description

For each user, I'm trying to figure out: How many accounts they have, and how many total transactions and actions have been taken across all accounts. In other words, I'm trying to generate a query whose results will look like the following:

User            Accounts          Transactions            Actions
----            --------          ------------            -------
Bill            2                 27                      7
Jack            1                 7                       0
Joe             0                 0                       0

How do I write a query like this? Currently, I'm trying the following:

SELECT
  u.Name,
  (SELECT COUNT(ID) FROM Account a WHERE a.UserID = u.ID) as Accounts
FROM
  User u

Now, I'm stuck though.

Upvotes: 0

Views: 57

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656471

As probably intended:

SELECT u.name, u.id
      ,COALESCE(x.accounts, 0)     AS accounts
      ,COALESCE(x.transactions, 0) AS transactions
      ,COALESCE(x.actions, 0)      AS actions
FROM  users u
LEFT  JOIN (
   SELECT a.userid  AS id
         ,count(*)  AS accounts
         ,sum(t.ct) AS transactions
         ,sum(c.ct) AS actions
   FROM   account a
   LEFT   JOIN (SELECT accountid AS id, count(*) AS ct FROM transaction GROUP BY 1) t USING (id)
   LEFT   JOIN (SELECT accountid AS id, count(*) AS ct FROM action      GROUP BY 1) c USING (id)
   GROUP  BY 1
   ) x USING (id);

Group first, join later. That's fastest and cleanest by far if you want the whole table.

SQL Fiddle (building on the one provided by @Raphaël, prettified).

Aside: I tripped over your naming convention in my first version.

Upvotes: 0

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

untested, I would go for something like this.

select
u.Name,
count(distinct a.ID) as Accounts,
count(distinct t.ID) as Transactions,
count(distinct ac.ID) as Actions
from User u
left join Account a on u.ID = a.UserID
left join Transaction t on t.AccountID = a.ID
left join Action ac on ac.AccountId = a.Id
group by u.Name

Upvotes: 1

Related Questions