Mixing INs and Cross Table queries in Postgres

Question

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.

Raphaël Althaus · Answer 1 · 2014-05-22T14:42:01.623

1

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

edited May 22 '14 at 14:42

answered May 22 '14 at 13:18

Raphaël Althaus

59,727
6
96
122

Good answer. To use User as a table name you can use special quotes: \`User\`. And use COALESCE to show 0 instead of NULL. (Moreover the user name should be unique, but I guess it is, what else would be the table's natural key?) – Thorsten Kettner May 22 '14 at 13:40
@ThorstenKettner Yup for backticks. But there's no need to coalesce with Count(distinct...) : see http://sqlfiddle.com/#!15/f45ae/6 – Raphaël Althaus May 22 '14 at 13:42
Yes, stupid me. Of course COUNT will be zero, never NULL. I must be tired. Sorry. – Thorsten Kettner May 22 '14 at 13:48
@ThorstenKettner oh, nothing to be sorry about ;) – Raphaël Althaus May 22 '14 at 13:59
This is a *very expensive* way. Multiple left joins on n-tables multiply result rows. `DISTINCT` repairs, but no need to go the expensive route. Compare: http://stackoverflow.com/questions/12464037/two-sql-left-joins-produce-incorrect-result/12464135#12464135 And backticks are MySQL non-standard stuff. Simply wrong in standard SQL or Postgres. – Erwin Brandstetter May 22 '14 at 14:18
@ErwinBrandstetter ok for bakticks. And I trust you for the "very expensive way". Could you just correct this sql fiddle : http://sqlfiddle.com/#!15/f45ae/9 with your code ? this might be the "Using", but I get some strange results with your answer... – Raphaël Althaus May 22 '14 at 14:39
@RaphaëlAlthaus: You got mixed up with two join condition. Your query fixed (plus mine prettified with COALESCE: http://sqlfiddle.com/#!15/f45ae/17 – Erwin Brandstetter May 22 '14 at 17:28
1

@ErwinBrandstetter Hmmm, I don't agree with your solution. Why would the relation between Account and Transaction use Transaction.Id, and not Transaction.AccountId ??? (same for Action) ? – Raphaël Althaus May 22 '14 at 17:32
@RaphaëlAlthaus: Fair point. I was assuming all tables are joined on ID. I don't usually use different names for fk & pk, that's a poor naming convention. I updated my answer according to your feedback. – Erwin Brandstetter May 22 '14 at 17:57

Erwin Brandstetter · Answer 2 · 2014-05-22T20:24:54.903

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.

Mixing INs and Cross Table queries in Postgres

2 Answers2