Cascading aggregates

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, '2025-1-2'),( 2, 1, '2025-1-3'),( 3, 2, '2025-1-4'),( 4, 2, '2025-1-5'),( 5, 3, '2025-1-6'),

( 6, 3, '2025-1-7'),( 7, 4, '2025-1-8'),( 8, 5, '2025-1-9'),( 9, 5, '2025-1-9'),(10, 5, '2025-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 |

+---------+-------+--------+---------+------------+