Cascading aggregates

from the Artful Common Queries page


When you have parent-child-grandchild tables, eg companies, users, actions, and your query requirement is for per-parent aggregates from the child table and per-child aggregates from the grandchild table, then cascading joins yield spuriously multiplied counts, and correlated subqueries fail because the second correlated subquery cannot find a visible joining column.

One solution is to use derived tables. Assuming ...

DROP TABLE IF EXISTS companies,users,actions;
CREATE TABLE companies (id int, name char(10));
INSERT INTO COMPANIES VALUES(1,'abc ltd'),(2,'xyz inc');
CREATE TABLE users (id INT,companyid INT);
INSERT INTO users VALUES(1,1),(2,1),(3,1),(4,2),(5,2);
CREATE TABLE actions (id INT, userid INT, date DATE); 
INSERT INTO actions VALUES
( 1, 1, '2009-1-2'),( 2, 1, '2009-1-3'),( 3, 2, '2009-1-4'),( 4, 2, '2009-1-5'),( 5, 3, '2009-1-6'),
( 6, 3, '2009-1-7'),( 7, 4, '2009-1-8'),( 8, 5, '2009-1-9'),( 9, 5, '2009-1-9'),(10, 5, '2009-1-9');

then...
  • Join companies and users once to establish a derived company-user table.
  • Join them a second time, this time aggregating on users.id to retrieve user counts per company.
  • Join the first derived table to the actions table, aggregating on actions.id to retrieve actions per user per company:
Here is the SQL:

SELECT cu1.cname as CpyName, cu2.uCnt as Users, ua.uid as UserNo, ua.aCnt as Actions, ua.Latest
FROM (
  SELECT c.id AS cid, c.name AS cname, u1.id AS uid 
  FROM companies c
  INNER JOIN users u1 ON u1.companyid=c.id
) AS cu1
INNER JOIN (
  SELECT c.id AS cid, COUNT(u2.id) AS uCnt
  FROM companies c
  INNER JOIN users u2 ON u2.companyid=c.id
  GROUP BY c.id
) AS cu2 ON cu1.cid=cu2.cid
INNER JOIN (
  SELECT u3.id AS uid, COUNT(a.id) AS aCnt, MAX(a.date) AS latest
  FROM users u3
  INNER JOIN actions a ON u3.id=a.userid
  GROUP BY u3.id
) AS ua ON ua.uid=cu1.uid; 
+---------+-------+--------+---------+------------+
| CpyName | Users | UserNo | Actions | Latest     |
+---------+-------+--------+---------+------------+
| abc ltd |     3 |      1 |       2 | 2009-01-03 |
| abc ltd |     3 |      2 |       2 | 2009-01-05 |
| abc ltd |     3 |      3 |       2 | 2009-01-07 |
| xyz inc |     2 |      4 |       1 | 2009-01-08 |
| xyz inc |     2 |      5 |       3 | 2009-01-09 |
+---------+-------+--------+---------+------------+


Return to the Artful Common Queries page