sqls

1.sql

SELECT
  r.id as r_id,
  i.id as i_id,
  SUM(u.quantity) as per_resident_total
FROM
  resident as r,
  inventory_item as i,
  inventory_usage as u
WHERE
  resident.id = inventory_usage.id AND
  inventory_item.id = inventory_usage.inventory_item_id AND
  inventory_item_usage.deleted IS NULL
GROUP BY
  r.id,
  i.id;

2.sql

SELECT
  t1.*,
  t2.per_item_total
FROM (
  SELECT
    r.id as r_id,
    i.id as i_id,
    SUM(u.quantity) as per_resident_total
  FROM
    resident as r,
    inventory_item as i,
    inventory_usage as u
  WHERE
    resident.id = inventory_usage.id AND
    inventory_item.id = inventory_usage.inventory_item_id AND
    inventory_item_usage.deleted IS NULL
  GROUP BY
    r.id,
    i.id
  ) AS t1
JOIN (
  SELECT
    i.id as i_id,
    SUM(u.quantity) as per_item_total
  FROM
    inventory_item as i,
    inventory_usage as u
  WHERE
    inventory_item.id = inventory_usage.inventory_item_id AND
    inventory_item_usage.deleted IS NULL
  GROUP BY
    i.id
  ) AS t2
ON t1.i_id = t2.i_id;