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, '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 | 2025-01-03 |
| abc ltd |     3 |      2 |       2 | 2025-01-05 |
| abc ltd |     3 |      3 |       2 | 2025-01-07 |
| xyz inc |     2 |      4 |       1 | 2025-01-08 |
| xyz inc |     2 |      5 |       3 | 2025-01-09 |
+---------+-------+--------+---------+------------+

Last updated 27 Jan 2025